IIF statement

[desi5pure]

Can I use two conditions in one IIF statement, for example, If DEPT_NUM is blank or NULL, make DEPT_NUM = '9999'.
I am having trouble with even just 1 statement, as following
select fld1, IIF(DEPT_NUM = NULL, "9999", DEPT_NUM), fld3
from Tbl_A

 

[dhookom]

You can't compare Null to any expression using = or Like or <> or ... You can use IsNull() or just Nz() for your needs:

SELECT fld1, Nz(DEPT_NUM, "9999") as DeptNum, fld3
FROM Tbl_A ;

Duane
Hook'D on Access
MS Access MVP

 

[desi5pure]

Thanks! That worked like a charm for NULL.

I checked web but can't see anything similar for blanks. Would Nz catch NULLs and blanks?

 

[PHV]

To catch null, blank and zero length:

SELECT fld1, IIf(Trim(DEPT_NUM & "")="", "9999", DEPT_NUM), fld3
FROM Tbl_A

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[desi5pure]

Thanks! It worked for blanks. Now I need to work on combining both in one select statement.
If fld2 is blank or NULL, DoThis Else DoThat.

 

[PHV]

It worked for blanks
As clearly stated in my previous post, it should work for blanks, null, zero length.

 

[desi5pure]

OK. Thanks! My apologies; didn't read first line.
This solves my problem. Once again, thanks to all who responded.