IIF with null field values

[Guest]

I have the following in a query:
HomeAd1Match: IIf(LMP![Home Addr 1]=SIMS!HomeAddress1,"ok","no match")

This works properly except for one piece. If both field values are null,
then it returns a "no match". But really, for my query I would want that to
return "OK" because both fields are empty.

How can I edit my IIF to have it report matching blanks as "OK"

Thank you

 

[Guest]

Try using the Nz function to change the Null value to 0

HomeAd1Match: IIf(Nz(LMP![Home Addr 1],0)=Nz(SIMS!HomeAddress1,0),"ok","no
match")

 

[Guest]

Wow, thanks. So simple- I'd never seen that function before.

Thanks for the prompt response.

Try using the Nz function to change the Null value to 0

HomeAd1Match: IIf(Nz(LMP![Home Addr 1],0)=Nz(SIMS!HomeAddress1,0),"ok","no
match")



--
HTH, Good Luck
BS"D

I have the following in a query:
HomeAd1Match: IIf(LMP![Home Addr 1]=SIMS!HomeAddress1,"ok","no match")

This works properly except for one piece. If both field values are null,
then it returns a "no match". But really, for my query I would want that to
return "OK" because both fields are empty.

How can I edit my IIF to have it report matching blanks as "OK"

Thank you

 

[Jeff Boyce]

Ofer's suggestion of the Nz() function will do it.

By the way, "blank" may not mean the same to you as to Access. If there is
nothing in the fields (i.e., the have no value, they are both "null"), then
trying compare them would return a False, since you can't compare what isn't
there.

Regards

Jeff Boyce
Microsoft Office/Access MVP