IsNull function

[Bob Matthews]

Under Office-97 I used the IsNull function often in taking burial records
and producing a narrative.

When I use the same formulae in the same queries under Office 2003 the
results go awry.

e.g. using a text field called DateDeath

Original Query: IIf(IsNull([DateDeath]),"","Died "&[DateDeath])
OK under Office-97 but not under Office 2003

but if I replace IsNull([DateDeath]) by [DateDeath] = ""
the query works.

Can anybody explain why this is so ?

Bob M

 

[Tom Lake]

Original Query: IIf(IsNull([DateDeath]),"","Died "&[DateDeath])

OK under Office-97 but not under Office 2003

but if I replace IsNull([DateDeath]) by [DateDeath] = ""
the query works.

"" is not the same as Null. "" means that there's a zero-length string
stored there. Null means that data has never been stored there.

Tom Lake

 

[[MVP] S.Clark]

A Null and a Zero-Length String are not the same. So, I prefer to do
something like this.

If Len(Nz([Fieldname]),"") = 0 then
'No data
Else
'Has data
Endif

 

[Michel Walsh]

Hi,


A NULL is different than a string with no character in it (also called a
null-string or a null string). It is probably that your [DateDeath] is a
null string, not a null. That may comes from a default value in the table
design, for that field, checking if null are allowed and, or, if null
strings are allowed.

To cover both cases, null and null string, you can use:

iif( 0 = len( dateDeath & "") , "", "Died " & DateDeath )


Hoping it may help,
Vanderghast, Access MVP