Access Query - IIF - Specific criteria from multiple fields

[Krysta]

Hello!

I have a table (GGB Report) with 2 fields of data that I would like to base an IIF statement upon. If MOVE TYPE field = "Export" AND PLACE OF RECEIPT field = blank, then I would like to enter "CY". I have used the following in my query and it is not working. I am doing an update table query and I have this in my "Update To" row. My Criteria row shows "". Any ideas on something different I can try? Thank you so much.

IIf([GGB Report].[MOVE TYPE]="EXPORT" And [GGB Report].[PLACE OF RECEIPT NAME]="","CY","")

 

[welshgasman]

[Place of receipt] could be Null?, so use the Null() function

 

[JonXL]

I think it's Nz(). So,

Nz([GGB Report].[PLACE OF RECEIPT NAME],"")=""

 

[welshgasman]

I think it's Nz(). So,

Nz([GGB Report].[PLACE OF RECEIPT NAME],"")=""

Oops, sorry, yes, I meant NZ().

Not sure where my head was this morning. Sorry.

 

[Micron]

You want to be careful updating a field to a zls (") assuming your design allows it. If you later query that field and need to include Null in its criteria, you won't return records where that field contains "" unless you specify both. Probably better to update to Null.

 

[Krysta]

Thank you all so much! Replacing "IIF" with "NZ" did the trick! Greatly appreciate the help.

 

[JonXL]

If you fully replace it, then you won't have the criterion for [GGB Report].[MOVE TYPE]="EXPORT". Would that not be a problem?

 

[Krysta]

If you fully replace it, then you won't have the criterion for [GGB Report].[MOVE TYPE]="EXPORT". Would that not be a problem?

Thanks so much for mentioning that. I didn't realize it had lost that criterion. The below worked perfectly. Really appreciate your help!

IIf([GGB Report].[MOVE TYPE]="EXPORT" And Nz([GGB Report].[PLACE OF RECEIPT NAME],"")="","CY")