Replies

To be honest, once you get the hang of them, they are just as easy as Access IIF’s.
In fact if you have multiple criteria then they can be a lot easier than the Access nested IIF functions.

LOL! Well you can count on the fact that I’ll be looking for help when I branch into these.

Leesha

I haven’t had to do any conditional statements yet! I think I’ll try to avoid those. I do agree that seeing both the grid and sql in the same view is nice. I’m much more comfortable in Access, however am having to learn SQL Server in order to use asp.net for websites. It’s been a struggle to say the least, but I do love a challenge.

Thanks!
Leesha

I meant when in the query builder view, SQL is vertical vs access being horizontal. I’m horrid at writing code so I depend on the query builder, although SQL servers is much easier to write than Access.

Leesah

Thanks! The first option worked perfectly. I’m so used to Access Queries being horizontal, that setting up the sql queries vertically still messes me up.

Thanks again,
Leesha

If you just want to see an 8 where the field is null then use

ISNULL([dischargestatus],8) AS [DischargeStat] or Whatever you want to call the field

The IsNull function in SQL is a substitution function that uses the second parameter as a substitution for NULL in the first field argument.
If the field is NOT NULL then the normal field value will be used.
It is equivalent to the Access NZ function.

Hi!
I finally had a chance to try this out and of course I’m struggling. The rationale makes perfect sense, its writing it that I’m messing up on. The sql presently says
SELECT DischargeReason
FROM tblStudentStatus
ORDER BY StatusDefined DESC

No matter where I insert the code you gave me I get an error or no change. I’m sure I’m doing it wrong. The goal is for DischargeReason to = 8 if it is null. I tried:

SELECT DischargeReason
FROM tblStudentStatus
ORDER BY StatusDefined DESC
WHERE ISNULL([DischargeReason],8)

Leesha

Tell me about it! I spent all day chasing around an error only to find out that is means that I need to have the database convert a null value to an integer! I seriously feel like you little .gif guy.

So, how what would the syntax look like in sql server if I want to convert a [dischargestatus] to the number 8 if the field is null?

Thanks!
Leesha

I went in and tried taking out the ()but got the same error. That is when I noted I had spelled the table name wrong! Fixed that it works fine. Thanks so much to both of you!
Leesha

Not sure what is going on there sorry.
Just stick with the one that works.

I keep getting an incorrect syntax near ‘)’. I’ve tried all sorts of combo’s but can’t get it. I’ll keep plugging but in the meantime wanted to say thank you so much!!!

Leesha

All three are nvarchar. That last code gave me a similar error to the print screen that I uploaded earlier. I did change the table name to be the name of my table but still got the error.

I’m sorry, I’m not familiar with SQL Server.

NP!! God only knows you’ve helped me with just about everything else I’ve had over the years!

Thanks Andrew, this did the trick! I did get an error when I tried the piece with the nulls. I copied exactly as you had but got an error stating invalid or missing expression.

I thought you were running the query from Access, where & is the concatenation operator; you can’t use that in SQL Server. You need + there.

What is the purpose of the letters N in the original code?

LastName + N‘,’ + N‘ ‘ + FirstName + N‘ ‘ + Mi AS [Student Name]

It was the only way that I could get it to have spaces between the names. How should I have it written?

Your query looks suspect to me. But some questions:
>>

Are you running that query in SQL Server as a view?

Yes – it’s being used with an asp.net page so its being run as a view from Sql server.

>>Or are you running it as a pass-through query in Access?

I have a similar on in an access project that runs fine.

>>

What looks suspect to me is the LastName + N’,’ + N’ ‘ + FirstName + N’ ‘ + Mi

Well that’s just my lame attempt from trial and error to get the return data to be cancantuated (I know I butchered that word!) to be lastname, firstname MI —- I needed the spaces.

I pasted it in and ran it and this is the error and how it got converted back. I’m attaching a print screen

>>You will also have to set the default to 0 (zero) in Sql Server table design.

Thanks Pat! This is the info I needed. The zero was the key!!!

Leesha