IIF Statement not working as expected

  • Thread starter Thread starter Jen Scott
  • Start date Start date
J

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!!
 
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
 
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
'====================================================
 
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.
 
Back
Top