Adding a "not is null" to my query

[Shales]

Hi Everyone,

Looking for some help. Here is part of my current query;



+IIf(Format([DOB],"dd/mm/yyyy")=Format([DateOfBirth],"dd/mm/yyyy"),1,0)

This is just part of my Expression Builder. What I am trying to do is count various matches in my data. It works, except for it will match records where both DOB fields are nulls. How can I ignore a match if they are NULL?

Thanks
Gavin

[ranman256]

In the query, filter out the nulls.

[Shales]

I don't think that will work for what I am trying to do. By filtering out will remove the lines completely and I need them in still because they might match on other criteria. I just need NULLs to be ignored in the count.

[JoeM]

Try using the NZ function to assign the NULLS a value, and choose different values for each one, i.e.

+IIf(Format(NZ([DOB],1),"dd/mm/yyyy")=Format(NZ([DateOfBirth],2),"dd/mm/yyyy"),1,0)

[Shales]

Try using the NZ function to assign the NULLS a value, and choose different values for each one, i.e.

+IIf(Format(NZ([DOB],1),"dd/mm/yyyy")=Format(NZ([DateOfBirth],2),"dd/mm/yyyy"),1,0)

Perfect!, Thank you very much Joe.

[JoeM]

You are welcome!