By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,072 Members | 1,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,072 IT Pros & Developers. It's quick & easy.

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

P: 62
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:
Expand|Select|Wrap|Line Numbers
  1. =IIf([Form]![f_MainReferralForm]![ActivityType_ID]=9,(1 Or 2 Or 7),(3 Or 7))
  2.  
This translates to the following SQL:

Expand|Select|Wrap|Line Numbers
  1. SELECT t_ReferralRoles_LU.ReferralRole_ID, t_ReferralRoles_LU.ReferralRole
  2. FROM t_ReferralRoles_LU
  3. 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))))
  4. ORDER BY t_ReferralRoles_LU.ReferralRole_ID;
  5.  
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
Dec 21 '11 #1

✓ answered by NeoPa

Try for line #3 (The WHERE clause) :

Expand|Select|Wrap|Line Numbers
  1. WHERE (([Form]![f_MainReferralForm]![ActivityType_ID]=9) AND ([ReferralRole_ID] In(1,2,7))
  2.    OR  ([Form]![f_MainReferralForm]![ActivityType_ID]<>9) AND ([ReferralRole_ID] In(3,7)))

Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,421
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?
Dec 21 '11 #2

P: 62
Hello Rabbit,

Yes, that is what I am trying to accomplish.
Dec 21 '11 #3

NeoPa
Expert Mod 15k+
P: 31,709
Try for line #3 (The WHERE clause) :

Expand|Select|Wrap|Line Numbers
  1. WHERE (([Form]![f_MainReferralForm]![ActivityType_ID]=9) AND ([ReferralRole_ID] In(1,2,7))
  2.    OR  ([Form]![f_MainReferralForm]![ActivityType_ID]<>9) AND ([ReferralRole_ID] In(3,7)))
Dec 21 '11 #4

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

Many thanks! :-)
Dec 21 '11 #5

P: 62
PS - I presume IN = includes?
Dec 21 '11 #6

NeoPa
Expert Mod 15k+
P: 31,709
Not exactly.
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 21 '11 #7

P: 62
Thanks for the clarification - can think of lots of ways to use this!
s-
Dec 21 '11 #8

NeoPa
Expert Mod 15k+
P: 31,709
Indeed :-)

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

Have fun.
Dec 21 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.