Is Not Null will not return falls value?

[anchorp]

Is Not Null will not return false value?

I have the following iif statement in a query:

IIf([jcphase.deptnum] Is Not Null,[jcphase.deptnum],[jcjob.deptnum]) AS calcdeptnum

It returns the correct value if the field is not null, however, it does not return the value in jcjob.deptnum if the field is null. What am I doing wrong? Any advise appreciated.

In case its relevant, the whole query is as follows:

SELECT jcdetail.jobnum, jcdetail.type, jcdetail.cost, jcdetail.date, jcjob.deptnum, jcphase.deptnum, IIf([jcphase.deptnum] Is Not Null,[jcphase.deptnum],[jcjob.deptnum]) AS calcdeptnum
FROM (jcdetail LEFT JOIN jcjob ON jcdetail.jobnum = jcjob.jobnum) LEFT JOIN jcphase ON (jcdetail.phasenum = jcphase.phasenum) AND (jcdetail.jobnum = jcphase.jobnum)
WHERE (((jcdetail.type)<>18) AND ((jcdetail.date)>#1/1/2009#));

 

[anchorp]

I also tried it the other way:

calcdeptnum: IIf([jcphase.deptnum] Is Null,[jcjob.deptnum],[jcphase.deptnum])

and I have the same problem.

I just need a way to get the jcjob.deptnum to show up if there is no value in jcphase.deptnum field.

Thanks!

 

[Galaxiom]

IIf(IsNull([jcphase.deptnum]),[jcjob.deptnum],[jcphase.deptnum])

 

[boblarson]

The thing to remember is that sometimes what LOOKS like a null field is not really a null field. It may have a ZERO LENGTH STRING ("") in there which looks like a null but is not a null. So, a good thing to do is to test for nulls AND Zero Length Strings. The way I do it (and there are many ways you can do it) is to use:

If Len([FieldNameHere] & "") = 0 Then
   'This is either null or has a zls
End If