(ASP & MS SQL 2000) How do I recognize a NULL out of a SELECT statement?

msimmons

07-22-2002, 05:31 PM

How do I recognize a NULL out of a SELECT statement? I need to determine when a field is null but I cant seem to get it. I tried "if len(field) = 0" and "if field = NULL" but neither would fire.
Thanks in advance,
Michael

allida77

07-22-2002, 05:37 PM

try

Is Null
or
Is Not Null

msimmons

07-22-2002, 05:47 PM

I tried that... "if field is null" hits every time (even when not null) I also tried "if field = """ and "if field = false" and they don't hit even when null :(
any other ideas?
Thanks
Michael

msimmons

07-22-2002, 06:56 PM

as a workaround I sorted the query by the field that I need to find the null and that worked for this situation (because I know how the data is constructed). But that wont work for many other situations.
Michael

Roy Sinclair

07-23-2002, 11:32 PM

If IsNull(variable) then ...

I've used this many times so I know it works.

whammy

07-23-2002, 11:59 PM

I use the same setup at work, and this almost always works for me:

WHERE fieldname IS NULL

Or, as an alternative (this usually works when the above doesn't):

WHERE fieldname = '' (that's two single quotes!)

:)