Access Query - IIF - Specific criteria from multiple fields

Krysta

New Member
Joined
Mar 24, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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","")
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
455
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I think it's Nz(). So,

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

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,085
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 24, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thank you all so much! Replacing "IIF" with "NZ" did the trick! Greatly appreciate the help.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
455
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Mar 24, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,274
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top