how to use isnull with the iif function?

[jgomez]

I have the field below in one of my queires. The M_OPT_DATA5 field has 5 possabilities one of them being blank... if its blank I'd like to return "NO CODE". How can I add that to what I have below???


COLLECTIONS: IIf([M_OPT_DATA5]="R","AL",IIf([M_OPT_DATA5]="C","AL",IIf([M_OPT_DATA5]="O","OFF",IIf([M_OPT_DATA5]="S","ARMS",if([M_OPT_DATA5""))))

[Fotis1991]

Hi. Try this

=IIf([M_OPT_DATA5]="R","AL",IIf([M_OPT_DATA5]="C","AL",IIf([M_OPT_DATA5]="O","OFF",IIf([M_OPT_DATA5]="S","ARMS",if([M_OPT_DATA5="";""))))


Hope that helps you.

[jgomez]

I replaced the ; with ,... doesn't work.

[Fotis1991]

=IIf([M_OPT_DATA5]="R","AL",IIf([M_OPT_DATA5]="C","AL",IIf([M_OPT_DATA5]="O","OFF",IIf([M_OPT_DATA5]="S","ARMS",if([M_OPT_DATA5="","NO CODE"))))

These with rd color, is true??...and work?

[jgomez]

see query one.

[Fotis1991]

Can you upload a sample workbook?

[jgomez]

Yes they do.

[Fotis1991]

Would you upload a sample workbook?

[Fotis1991]

Can't open your file.....

[jgomez]

ahhh... i can but i guess because its mine.

[Fotis1991]

..Propably...

[alansidman]

This works for me.

SELECT accts.ACCT, OPT.M_OPT_DATA5, IIf([M_OPT_DATA5]="R","AL",IIf([M_OPT_DATA5]="C","AL",IIf([M_OPT_DATA5]="O","OFF",IIf([M_OPT_DATA5]="S","ARMS",IIf([M_OPT_DATA5] Is Null,"NO CODE"))))) AS Expr1
FROM accts LEFT JOIN OPT ON accts.ACCT = OPT.ACCT;