Limiting the values of a combo box using IIF in query criteria

[Sandra Walsh]

Hello -

I am trying to limit the options that show in a combo box based on the value of another field.

In the design view of my query, I have the following criteria in the ReferralRole_ID field:

=IIf([Form]![f_MainReferralForm]![ActivityType_ID]=9,(1 Or 2 Or 7),(3 Or 7))

This translates to the following SQL:

SELECT t_ReferralRoles_LU.ReferralRole_ID, t_ReferralRoles_LU.ReferralRole
FROM t_ReferralRoles_LU
WHERE (((t_ReferralRoles_LU.ReferralRole_ID)=IIf([Form]![f_MainReferralForm]![ActivityType_ID]=9,((t_ReferralRoles_LU.ReferralRole_ID)=1 Or (t_ReferralRoles_LU.ReferralRole_ID)=2 Or (t_ReferralRoles_LU.ReferralRole_ID)=7),((t_ReferralRoles_LU.ReferralRole_ID)=3 Or (t_ReferralRoles_LU.ReferralRole_ID)=7))))
ORDER BY t_ReferralRoles_LU.ReferralRole_ID;

I am getting zero records in my combo.

If I just put < 1 OR 2 OR 7 > in the criteria, it works fine.

Not sure if the IIF statement is coded correctly.

Many thanks
Sandra

[Rabbit]

What it sounds like you're looking for is along the lines of this:

If Activity Type is 9, then Referral Role ID can be 1, 2, or 7. Otherwise Referral Role ID can be 3 or 7. Is that correct?

[Sandra Walsh]

Hello Rabbit,

Yes, that is what I am trying to accomplish.

[NeoPa]

Try for line #3 (The WHERE clause) :

WHERE (([Form]![f_MainReferralForm]![ActivityType_ID]=9) AND ([ReferralRole_ID] In(1,2,7))
   OR  ([Form]![f_MainReferralForm]![ActivityType_ID]<>9) AND ([ReferralRole_ID] In(3,7)))

[Sandra Walsh]

Works Perfectly! I was not aware of the In(x,y, ...) option.

Many thanks! :-)

[Sandra Walsh]

PS - I presume IN = includes?

[NeoPa]

Not exactly.

WHERE [A] In(X,Y,Z)

This could be read out as :
Where the value of [A] is found in the list of X,Y,Z.

It can work with any type of value. Not just numeric but also Dates, Strings etc.

[Sandra Walsh]

Thanks for the clarification - can think of lots of ways to use this!
s-

[NeoPa]

Indeed :-)

In() and Like can be very powerful constructs indeed.

Have fun.