IIF with null field values

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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")
 
Wow, thanks. So simple- I'd never seen that function before.

Thanks for the prompt response.

Ofer Cohen said:
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


kayabob said:
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
Click to expand...
Click to expand...
 
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
 
Back
Top