Using IIF to pass query criteria

[Jason M]

Good morning all! I have searched the archive and didn't find an exact
answer to my question, so I am posing this quesiton to the group:

I am trying to use IIf([forms].[PreviewReports].[chkpumpstations]=0,<>7,7)
but in reading through the posts here I understand that the IIF statemtn may
not pass the <> not equal to characters to my query. I have also tried:
IIf([forms].[PreviewReports].[chkpumpstations]=0,"Not 7",7) and various
other combinations like it to no avail.

What I would like to do is if the mentioned check box is active (true)
collect only equipment with an CategoryId of 7 else I want all of the
equipment EXCEPT 7.

I have entered <>7 into the criteria line and it works fine as does not 7,
is there a way that I can use the IIF statement to pass the correct
statement to the query?

Thanks, Jason

 

[ghetto_banjo]

iif statements can only return values, not expressions. However, we
can accomplish what you want by altering the where clause of your
query (or you could do it in the query design view in the criteria by
using the multiple lines for the OR part)


example

Select * From yourTable
WHERE ([forms]![PreviewReports].[chkpumpstations]=0 AND CategoryID <>
7) OR ([forms]![PreviewReports].[chkpumpstations]<>0 AND [CategoryID]
= 7);

 

[John Spencer]

You could try an expression like the following:

IIF([forms].[PreviewReports].[chkpumpstations]=0,CategoryID =7,CategoryID <>7)

In the query design view you would have that expression in a field "cell" and
then the criteria under the expression would be True.

Another way (probably faster) would be to add the reference to the control
into a field "cell" and set up the criteria as follows

Field: [forms].[PreviewReports].[chkpumpstations]
Criteria (line 1): TRUE
Criteria (line 2) : False

Field: CategoryID
Criteria (line 1): =7
Criteria (line 2) : <> 7

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jason M]

That's the trick! Thanks a ton for the help! Your solution also has the
added advantage of being a bit easier to read as well...

Have a great friday!

Jason