IIF Statement not working as expected

[Jen Scott]

In a query in Access 2007, I'm using the following nested IIF statement:

App Status: IIf([Ace Stat Cd]="NEWACCT","Approved",IIf([Ace Stat
Cd]="VOIDAPP" Or "MANLDEC" Or "AUTODEC","Declined","Pending"))

I know there are other values in my [Ace Stat Cd] field, and I should be
returning "Pending" for quite a few records, but it is marking the "Pending"
records as "Declined"....

What am I doing wrong? Thanks!!

 

[vanderghast]

try:

App Status: IIf([Ace Stat Cd]="NEWACCT","Approved",IIf([Ace Stat
Cd] IN("VOIDAPP" ,"MANLDEC" ,"AUTODEC"),"Declined","Pending"))


The problem seems to be that while the shortcut notation:

= 4 OR 5 OR 6

is possible in SOME places (such as for simple criteria in the query grid),
it is far safer to use the long notation

x=4 OR x=5 OR x = 6

or simply

x IN(4, 5, 6)


Vanderghast, Access MVP

 

[John Spencer]

You must include a full comparison for each OR.

IIf([Ace Stat Cd]="NEWACCT","Approved",
IIf([Ace Stat Cd]="VOIDAPP" Or [Ace Stat Cd]="MANLDEC" Or [Ace Stat
Cd]="AUTODEC","Declined","Pending"))

An alternative would be to use IN

IIf([Ace Stat Cd]="NEWACCT","Approved"
,IIf([Ace Stat Cd] IN ("VOIDAPP","MANLDEC","AUTODEC"),"Declined","Pending"))




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Duane Hookom]

Data belongs in your tables, not your expressions...
http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx

I would create a small lookup table which should be easier to maintain than
expressions.

tblCodeStatus
==================
AceStatCd text primary key
AppStatus text

Append every unique [Ace Stat Cd] value and update the AppStatus field as
necessary.

Then get rid of nasty IIf() and join the new table into your query so you
can display the appropriate App Status.