IIf statement not working

[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

 

[vanderghast]

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

 

[Duane Hookom]

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.

 

[Mary Beth]

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.

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

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

 

[vanderghast]

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

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.

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

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