Query Criteria 1

[C4rtm4N]

I have a rather complicated (for a newbie like me anyway)query that I'm wanting to apply some additional criteria to but don't seem to be able to get it correct.

I've set up a form with several radio buttons on so that a user can choose whether to filter query results by certain data or not. I've tried linking the field on the form that's altered to the criteria section of the query but Access keeps telling me that it's too complicated (one of the syntaxes that I've tried is below - I've also tried using braketc, quotes, etc around result 2)

IIf([Forms]![Productivity_Dates]![Staff Filter]=3,"T",IIf([Forms]![Productivity_Dates]![Staff Filter]=2," " or "" or Is Null or "S" or "A","S"))

Manually inputting any of the 3 iif outputs into the query has the desired affect but I can't allow users to do this.

Any ideas ?

In addition is there any way that I can add an option that uses a null value that won't filter the query (the field being filtered could be null or have any alpha character in it)?

Thanks in Advance

Steve

 

[PHV]

I'm afraid you have to tweak the SQL code:
... WHERE ...
(([Forms]![Productivity_Dates]![Staff Filter]=3 And yourField='T') Or ([Forms]![Productivity_Dates]![Staff Filter]=2 And Trim(yourField & "") In ('','A')) Or yourField='S')
...


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244