Query filtered by Option Group - how to Show All?

[Katie Howard]

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!

[Rabbit]

Where's the false parameter of the iif function? That by itself shouldn't work, it should error out.

[Katie Howard]

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

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:

SELECT DISTINCT Sessions.ID, Sessions.CFPTitle, Sessions.Organization, Sessions.CFPac, Sessions.CFPfocus, Review.Status, Events.AC
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
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()))
ORDER BY Sessions.Organization;

[Rabbit]

Please use code tags when posting code.

You should be doing something like this:

SELECT *
FROM someTable
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.

[Katie Howard]

That works perfectly! Thank you SO much!!