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

Query filtered by Option Group - how to Show All?

P: 23
Hi,

I'm running Access 2010 and have a form with an option group that people can click to filter a query (which displays in a list box) with "Accepted", "Declined" or "Show All".

It works great if I choose the Accepted option (value of 1) or the Declined option (value of 4), but I'm having trouble making the Show All option work (value of 99) - it doesn't display any results. Can you help me? The criteria that I'm using for the field in my query is:

IIf([Forms]![Main_form]![Filter_grp]<99,[Forms]![Main_form]![Filter_grp])

Thanks!
May 16 '12 #1

✓ answered by Rabbit

Please use code tags when posting code.

You should be doing something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM someTable
  3. WHERE someField = IIf(someParameter <> allValue, someParameter, someField)
Return the field value in the false parameter. That way it will always be true, barring any null values. If you have nulls, you will need to Nz() your field.

Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,315
Where's the false parameter of the iif function? That by itself shouldn't work, it should error out.
May 16 '12 #2

P: 23
Well, I don't have one. :) I'd also tried this:

Expand|Select|Wrap|Line Numbers
  1. IIf([Forms]![Main_form]![Filter_grp]<>99,[Forms]![Main_form]![Filter_grp],(1 Or 2 Or 3 Or 4 Or 5))
But that doesn't work, either. It works great if I only have 1 value for the false part (eg, 1), but I'm not able to put in multiple values with the "or" operator. Is there a better way to do this?

This is the SQL, if that helps:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Sessions.ID, Sessions.CFPTitle, Sessions.Organization, Sessions.CFPac, Sessions.CFPfocus, Review.Status, Events.AC
  2. FROM (Sessions INNER JOIN Events ON Sessions.Event = Events.AC) LEFT JOIN (Review LEFT JOIN Status ON Review.Status = Status.StatusID) ON Sessions.ID = Review.Session_ID
  3. WHERE (((Review.Status)=IIf([Forms]![Main_form]![Filter_grp]<>99,[Forms]![Main_form]![Filter_grp],((Review.Status)=1 Or (Review.Status)=2 Or (Review.Status)=3 Or (Review.Status)=4 Or (Review.Status)=5))) AND ((Review.User)=GetUserName()))
  4. ORDER BY Sessions.Organization;
May 16 '12 #3

Rabbit
Expert Mod 10K+
P: 12,315
Please use code tags when posting code.

You should be doing something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM someTable
  3. WHERE someField = IIf(someParameter <> allValue, someParameter, someField)
Return the field value in the false parameter. That way it will always be true, barring any null values. If you have nulls, you will need to Nz() your field.
May 16 '12 #4

P: 23
That works perfectly! Thank you SO much!!
May 16 '12 #5

Post your reply

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