IIf statement not working

  • Thread starter Thread starter Mary Beth
  • Start date Start date
M

Mary Beth

I have the following IIF statement in the field line of a select query that I
can't get to work. This is an option box that is a text field:

Agency: IIf(CInt([strAgencyProgram])=1 Or 2 Or 3,"ODJFS",
IIf(CInt([strAgencyProgram])=9,"State Plan",
IIf(CInt([strAgencyProgram])=10,"State Plan MH")))

Access recognizes the 1, 2, or 3, but not the 9 or 10. All records are
listed as 'ODJFS.

Can someone help?

Thanks,
Mary Beth
 
Try:

Agency: IIf(CInt([strAgencyProgram])= IN(1, 2, 3),"ODJFS",
IIf(CInt([strAgencyProgram])=9,"State Plan",
IIf(CInt([strAgencyProgram])=10,"State Plan MH")))



The problem was not with the iif, but with

x = 1 OR 2 OR 3


which is somehow accepted in the query designer GRID as CRITERIA, but not as
statement. It should be, as statement:

x=1 OR x=2 OR x=3

or using the IN construction, as already suggested.



Vanderghast, Access MVP
 
IMHO, an expression like this in a query is not the place to create business
rules. You should have a table of programs and codes that you can add to your
query. You should be maintaining data in tables, not code and expressions.
 
Brilliant. This works well except that you have to take the = out of the
expression before the 'IN'. After I took it out, I got exactly what I needed.

vanderghast said:
Try:

Agency: IIf(CInt([strAgencyProgram])= IN(1, 2, 3),"ODJFS",
IIf(CInt([strAgencyProgram])=9,"State Plan",
IIf(CInt([strAgencyProgram])=10,"State Plan MH")))



The problem was not with the iif, but with

x = 1 OR 2 OR 3


which is somehow accepted in the query designer GRID as CRITERIA, but not as
statement. It should be, as statement:

x=1 OR x=2 OR x=3

or using the IN construction, as already suggested.



Vanderghast, Access MVP



Mary Beth said:
I have the following IIF statement in the field line of a select query that
I
can't get to work. This is an option box that is a text field:

Agency: IIf(CInt([strAgencyProgram])=1 Or 2 Or 3,"ODJFS",
IIf(CInt([strAgencyProgram])=9,"State Plan",
IIf(CInt([strAgencyProgram])=10,"State Plan MH")))

Access recognizes the 1, 2, or 3, but not the 9 or 10. All records are
listed as 'ODJFS.

Can someone help?

Thanks,
Mary Beth
Click to expand...
Click to expand...
 
You are right about the = sign, it must be removed when you use IN:

Agency: IIf(CInt([strAgencyProgram]) IN(1, 2, 3),"ODJFS",
IIf(CInt([strAgencyProgram])=9,"State Plan",
IIf(CInt([strAgencyProgram])=10,"State Plan MH")))




Note that as stated by Duane, a better solution could be to put all those
constants in a table:

refAgencyCode ' table name
code codeName ' fields
1 ODJFS
2 ODJFS
3 ODJSF
9 State Plan
10 State Plan MH ' data


and then, bring that reference table in the query, make an inner join
between strAgencyProgram (str... as in integer? ) and refAgencyCode.code,
and get the field codeName rather than through a computed expression.

The advantage: if any modification is required, it is done by playing with
DATA in a TABLE (easy, through a form), and NOT by modifying SQL CODE.


Vanderghast, Access MVP



Mary Beth said:
Brilliant. This works well except that you have to take the = out of the
expression before the 'IN'. After I took it out, I got exactly what I
needed.

vanderghast said:
Try:

Agency: IIf(CInt([strAgencyProgram])= IN(1, 2, 3),"ODJFS",
IIf(CInt([strAgencyProgram])=9,"State Plan",
IIf(CInt([strAgencyProgram])=10,"State Plan MH")))



The problem was not with the iif, but with

x = 1 OR 2 OR 3


which is somehow accepted in the query designer GRID as CRITERIA, but not
as
statement. It should be, as statement:

x=1 OR x=2 OR x=3

or using the IN construction, as already suggested.



Vanderghast, Access MVP



Mary Beth said:
I have the following IIF statement in the field line of a select query
that
I
can't get to work. This is an option box that is a text field:

Agency: IIf(CInt([strAgencyProgram])=1 Or 2 Or 3,"ODJFS",
IIf(CInt([strAgencyProgram])=9,"State Plan",
IIf(CInt([strAgencyProgram])=10,"State Plan MH")))

Access recognizes the 1, 2, or 3, but not the 9 or 10. All records are
listed as 'ODJFS.

Can someone help?

Thanks,
Mary Beth
Click to expand...
Click to expand...
Click to expand...
 
Back
Top