We help IT Professionals succeed at work.

Handle Nulls in MS-Access IIF query

glendawi
glendawi asked
on
I have a MS-Access query.  I want to do the equivalent of a case statement in SQL

CASE USPS_ST in (CO, WY, NE) then Map_Zip else 99999
CASE USPS_ST in (CO, WY, NE)  and Zip_Code is null then 00000  

map_zip2: IIf([tbl_Patient].[USPS_St]<>"CO" And [tbl_Patient].[USPS_St]<>"WY" And [tbl_Patient].[USPS_St]<>"NE" Or IsNull([tbl_Patient]![USPS_St]),99999,CO_Geography]![Map_Zip])

I have 2 tables
1 Patient Data
1 Geography
They are left joined on Zip_code

So if the zip_code is null there is not a match and I want it to fill in the Map_Zip as 00000

I get the first part to work, but not the null zip code.

Help would be appreciated
Comment
Watch Question

Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unfortunately, all portions of an IIF are evaluated, so you sometimes have issues working with NULLs.

You can try using the Nz function to work with NULLS. For example:

map_zip2: IIf([tbl_Patient].[USPS_St]<>"CO" And Nz([tbl_Patient].[USPS_St],"")<>"WY" And Nz([tbl_Patient].[USPS_St],"")<>"NE" Or IsNull([tbl_Patient]![USPS_St]),99999,CO_Geography]![Map_Zip])

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
<I want to do the equivalent of a case statement in SQL>

...just curious...
Then why not just do this as a case (or IF-then-else) statement in a code function?

Whenever my IIF formulas get beyond the basic syntax, I immediately build a vba function instead.

Yes, ...you will have to deal with the nulls, (or zero length strings, as the case may be), but at least with code it should be easier to construct and troubleshoot the AND/OR logic

With code you can troubleshoot easier, create comments, add error handling, ...etc
...In addition to the other benefits of creating functions (re-usability, transportability, ...etc)

Again, I'm just curious...

Jeff