Prev: concatenate
Next: Counting Ocurrances within a Time Frame
From: Jason M on 2 Apr 2010 10:45
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


From: ghetto_banjo on 2 Apr 2010 11:12
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);





From: John Spencer on 2 Apr 2010 11:13
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 wrote:
> 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
>
>
From: Marshall Barton on 2 Apr 2010 11:18
Jason M wrote:

>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?


You would need to include the entire expression:
IIf([forms].[PreviewReports].[chkpumpstations]=0,CategoryId
<> 7, CategoryId = 7)

But, IMO, that's kind of clumsy. I thing I would use:

WHERE (Forms.PreviewReports.chkpumpstations = 0 And
CategoryId <> 7) OR (Forms.PreviewReports.chkpumpstations
<> 0 And CategoryId = 7)

OTOH, You may be much better of using the OpenReport
method's WhereCondition argument instead of messing around
in the query.
--
Marsh
MVP [MS Access]



From: Jason M on 2 Apr 2010 11:22
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


"ghetto_banjo" <adam.vogg(a)gmail.com> wrote in message
news:6ce75688-f93f-4873-8ad5-c354fc418139(a)y14g2000yqm.googlegroups.com...
> 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);
>
>
>
>
>
>


 | 
Pages: 1
Prev: concatenate
Next: Counting Ocurrances within a Time Frame