Less then and greater then operators in iif criteria expression

A

AccessARS

I have 2 different variations of an Access query which is referencing fields
on an open form for criteria. Unfortunately the statement with the IIF
greater then or less then operators in the HAVING statement does not return
results and does not error out...

The following statement works:

SELECT WFP.BucketS, WFP.Bucket, Count(WFP.Bucket) AS CountOfBucket
FROM WFP
GROUP BY WFP.BucketS, WFP.Bucket, WFP.LOB, WFP.Lead1, WFP.ConCode
HAVING (((WFP.LOB) Like "*" & [Forms]![Overview]![Indicator1]) AND
((WFP.Lead1) Like "*" & [Forms]![Overview]![Indicator2]) AND
((WF_Part1.CompanyCodeID)<2000)));



The following statement with an IIF in the HAVING criteria statement of
ConCode does not return results with the greater then and less then operators
but if I remove the > or < operators it seems to work:

SELECT WFP.BucketS, WFP.Bucket, Count(WFP.Bucket) AS CountOfBucket
FROM WFP
GROUP BY WFP.BucketS, WFP.Bucket, WFP.LOB, WFP.Lead1, WFP.ConCode
HAVING (((WFP.LOB) Like "*" & [Forms]![Overview]![Indicator1]) AND
((WFP.Lead1) Like "*" & [Forms]![Overview]![Indicator2]) AND ((WFP.ConCode)
Like
IIf([Forms]![Overview]![tgl_ChartsInFocus]=1,(WFP.ConCode)<2000,(WFP.ConCode)>2000)));


?????
I have tried searching for similar issues in the forum but have not had any
luck.

Thank you in advance for your help.
 
J

Jeff Boyce

If you copied that SELECT statement and pasted it into a new (empty) query's
SQL window, will Access run it?

If a little confused by your use of "Like "*" & xxxxx -- this will only work
if what you are comparing to ENDS WITH "xxxxx" -- is that what it's supposed
to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

John Spencer

The following might work. The IIF expression will return true or false
based on the 2nd and 3rd arguments and the first argument determines
which of the two (2nd or 3rd) arguments to use.

Records are returned if you have a TRUE result and not returned if you
have a FALSE result.

SELECT WFP.BucketS, WFP.Bucket
, Count(WFP.Bucket) AS CountOfBucket
FROM WFP

WHERE WFP.LOB Like "*" & [Forms]![Overview]![Indicator1] AND
WFP.Lead1 Like "*" & [Forms]![Overview]![Indicator2])
AND
IIf([Forms]![Overview]![tgl_ChartsInFocus]=1,WFP.ConCode<2000,WFP.ConCode>2000)

GROUP BY WFP.BucketS, WFP.Bucket, WFP.LOB, WFP.Lead1, WFP.ConCode

Another way that could be expressed would be

WHERE WFP.LOB Like "*" & [Forms]![Overview]![Indicator1] AND
WFP.Lead1 Like "*" & [Forms]![Overview]![Indicator2])
AND ((WFP.Concode < 2000 AND [Forms]![Overview]![tgl_ChartsInFocus]=1)
OR (WFP.Concode > 2000 AND [Forms]![Overview]![tgl_ChartsInFocus]<>1))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
A

AccessARS

Q1. If you copied that SELECT statement and pasted it into a new (empty)
query's SQL window, will Access run it?

A1. YES

Q2. I'm a little confused by your use of "Like "*" & xxxxx -- this will
only work if what you are comparing to ENDS WITH "xxxxx" -- is that what it's
supposed to do?

A2. The Like "*" & [Forms]![Overview]![Indicator2] statement give me
flexiblity to returns ALL records if [Indicator2] IsNull or only the value of
[Indicator2] when it is populated.


The statements below are the SQL string from an Access Query originally
built in Access Query Designer object. They have been working fine with the
Like "*" & ... expression until I added the IIF statement in question. Like
I stated below it does not error out but does not give me the desired results.

Thank you!

Jeff Boyce said:
If you copied that SELECT statement and pasted it into a new (empty) query's
SQL window, will Access run it?

If a little confused by your use of "Like "*" & xxxxx -- this will only work
if what you are comparing to ENDS WITH "xxxxx" -- is that what it's supposed
to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

AccessARS said:
I have 2 different variations of an Access query which is referencing
fields
on an open form for criteria. Unfortunately the statement with the IIF
greater then or less then operators in the HAVING statement does not
return
results and does not error out...

The following statement works:

SELECT WFP.BucketS, WFP.Bucket, Count(WFP.Bucket) AS CountOfBucket
FROM WFP
GROUP BY WFP.BucketS, WFP.Bucket, WFP.LOB, WFP.Lead1, WFP.ConCode
HAVING (((WFP.LOB) Like "*" & [Forms]![Overview]![Indicator1]) AND
((WFP.Lead1) Like "*" & [Forms]![Overview]![Indicator2]) AND
((WF_Part1.CompanyCodeID)<2000)));



The following statement with an IIF in the HAVING criteria statement of
ConCode does not return results with the greater then and less then
operators
but if I remove the > or < operators it seems to work:

SELECT WFP.BucketS, WFP.Bucket, Count(WFP.Bucket) AS CountOfBucket
FROM WFP
GROUP BY WFP.BucketS, WFP.Bucket, WFP.LOB, WFP.Lead1, WFP.ConCode
HAVING (((WFP.LOB) Like "*" & [Forms]![Overview]![Indicator1]) AND
((WFP.Lead1) Like "*" & [Forms]![Overview]![Indicator2]) AND
((WFP.ConCode)
Like
IIf([Forms]![Overview]![tgl_ChartsInFocus]=1,(WFP.ConCode)<2000,(WFP.ConCode)>2000)));


?????
I have tried searching for similar issues in the forum but have not had
any
luck.

Thank you in advance for your help.
Click to expand...
Click to expand...
 
A

AccessARS

Q1. If you copied that SELECT statement and pasted it into a new (empty)
query's SQL window, will Access run it?

A1. YES

Q2. I'm a little confused by your use of "Like "*" & xxxxx -- this will
only work if what you are comparing to ENDS WITH "xxxxx" -- is that what it's
supposed to do?

A2. The Like "*" & [Forms]![Overview]![Indicator2] statement give me
flexiblity to returns ALL records if [Indicator2] IsNull or only the value of
[Indicator2] when it is populated.


The statements below are the SQL string from an Access Query originally
built in Access Query Designer object. They have been working fine with the
Like "*" & ... expression until I added the IIF statement in question. Like
I stated below it does not error out but does not give me the desired results.

Thank you!


Jeff Boyce said:
If you copied that SELECT statement and pasted it into a new (empty) query's
SQL window, will Access run it?

If a little confused by your use of "Like "*" & xxxxx -- this will only work
if what you are comparing to ENDS WITH "xxxxx" -- is that what it's supposed
to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

AccessARS said:
I have 2 different variations of an Access query which is referencing
fields
on an open form for criteria. Unfortunately the statement with the IIF
greater then or less then operators in the HAVING statement does not
return
results and does not error out...

The following statement works:

SELECT WFP.BucketS, WFP.Bucket, Count(WFP.Bucket) AS CountOfBucket
FROM WFP
GROUP BY WFP.BucketS, WFP.Bucket, WFP.LOB, WFP.Lead1, WFP.ConCode
HAVING (((WFP.LOB) Like "*" & [Forms]![Overview]![Indicator1]) AND
((WFP.Lead1) Like "*" & [Forms]![Overview]![Indicator2]) AND
((WF_Part1.CompanyCodeID)<2000)));



The following statement with an IIF in the HAVING criteria statement of
ConCode does not return results with the greater then and less then
operators
but if I remove the > or < operators it seems to work:

SELECT WFP.BucketS, WFP.Bucket, Count(WFP.Bucket) AS CountOfBucket
FROM WFP
GROUP BY WFP.BucketS, WFP.Bucket, WFP.LOB, WFP.Lead1, WFP.ConCode
HAVING (((WFP.LOB) Like "*" & [Forms]![Overview]![Indicator1]) AND
((WFP.Lead1) Like "*" & [Forms]![Overview]![Indicator2]) AND
((WFP.ConCode)
Like
IIf([Forms]![Overview]![tgl_ChartsInFocus]=1,(WFP.ConCode)<2000,(WFP.ConCode)>2000)));


?????
I have tried searching for similar issues in the forum but have not had
any
luck.

Thank you in advance for your help.
Click to expand...
Click to expand...
 
J

Jeff Boyce

Your IIF() statement appears to tell Access to calculate

(WFP.ConCode)<2000

when the test condition = 1, or otherwise, to calculate

(WFP.ConCode)>2000

You've described "how" you're trying to do something. I'm still not
tracking on WHAT that something is.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP



AccessARS said:
Q1. If you copied that SELECT statement and pasted it into a new (empty)
query's SQL window, will Access run it?

A1. YES

Q2. I'm a little confused by your use of "Like "*" & xxxxx -- this will
only work if what you are comparing to ENDS WITH "xxxxx" -- is that what
it's
supposed to do?

A2. The Like "*" & [Forms]![Overview]![Indicator2] statement give me
flexiblity to returns ALL records if [Indicator2] IsNull or only the value
of
[Indicator2] when it is populated.


The statements below are the SQL string from an Access Query originally
built in Access Query Designer object. They have been working fine with
the
Like "*" & ... expression until I added the IIF statement in question.
Like
I stated below it does not error out but does not give me the desired
results.

Thank you!


Jeff Boyce said:
If you copied that SELECT statement and pasted it into a new (empty)
query's
SQL window, will Access run it?

If a little confused by your use of "Like "*" & xxxxx -- this will only
work
if what you are comparing to ENDS WITH "xxxxx" -- is that what it's
supposed
to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

AccessARS said:
I have 2 different variations of an Access query which is referencing
fields
on an open form for criteria. Unfortunately the statement with the IIF
greater then or less then operators in the HAVING statement does not
return
results and does not error out...

The following statement works:

SELECT WFP.BucketS, WFP.Bucket, Count(WFP.Bucket) AS CountOfBucket
FROM WFP
GROUP BY WFP.BucketS, WFP.Bucket, WFP.LOB, WFP.Lead1, WFP.ConCode
HAVING (((WFP.LOB) Like "*" & [Forms]![Overview]![Indicator1]) AND
((WFP.Lead1) Like "*" & [Forms]![Overview]![Indicator2]) AND
((WF_Part1.CompanyCodeID)<2000)));



The following statement with an IIF in the HAVING criteria statement of
ConCode does not return results with the greater then and less then
operators
but if I remove the > or < operators it seems to work:

SELECT WFP.BucketS, WFP.Bucket, Count(WFP.Bucket) AS CountOfBucket
FROM WFP
GROUP BY WFP.BucketS, WFP.Bucket, WFP.LOB, WFP.Lead1, WFP.ConCode
HAVING (((WFP.LOB) Like "*" & [Forms]![Overview]![Indicator1]) AND
((WFP.Lead1) Like "*" & [Forms]![Overview]![Indicator2]) AND
((WFP.ConCode)
Like
IIf([Forms]![Overview]![tgl_ChartsInFocus]=1,(WFP.ConCode)<2000,(WFP.ConCode)>2000)));


?????
I have tried searching for similar issues in the forum but have not had
any
luck.

Thank you in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
A

AccessARS

I have a form that provides users data in two different parts one side is the
data for Regions 1 and 2 in a tabular format and the other is the aggregate
charts which allows the user to toggle between Region 1 and 2.

By default, the data behind the tabular form and subsequent queries run and
display all leaders (considering that the fields [Indicator1] and
[indicator2] identify leadership and are Null by default) for both regions 1
and 2.

The query in question is driving one of several charts on display on the
Chart side, which also run all leaders ( i.e (((WFP.LOB) Like "*" &
[Forms]![Overview]![Indicator1]) AND...[Indicator2])..) but only focus on
region 1 by default. The statement in question is to respond to a toggle
button on top of the charts that will allow the user to select between region
"1" and "2" at which time the charts are refreshed/requeried to display the
appropriate breakdown. Therefore in the query is retreiving Region "1"
identified as [WFP].[CoCode]<2000 (Less Then 2000) and region "2" identified
as [WFP].[CoCode]>2000 (Greater Then 2000).
i.e
IIf([Forms]![Overview]![tgl_ChartsInFocus]=1,(WFP.ConCode)<2000,(WFP.ConCode)>2000...


I hope this helps….thanks again.

Jeff Boyce said:
Your IIF() statement appears to tell Access to calculate

(WFP.ConCode)<2000

when the test condition = 1, or otherwise, to calculate

(WFP.ConCode)>2000

You've described "how" you're trying to do something. I'm still not
tracking on WHAT that something is.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP



AccessARS said:
Q1. If you copied that SELECT statement and pasted it into a new (empty)
query's SQL window, will Access run it?

A1. YES

Q2. I'm a little confused by your use of "Like "*" & xxxxx -- this will
only work if what you are comparing to ENDS WITH "xxxxx" -- is that what
it's
supposed to do?

A2. The Like "*" & [Forms]![Overview]![Indicator2] statement give me
flexiblity to returns ALL records if [Indicator2] IsNull or only the value
of
[Indicator2] when it is populated.


The statements below are the SQL string from an Access Query originally
built in Access Query Designer object. They have been working fine with
the
Like "*" & ... expression until I added the IIF statement in question.
Like
I stated below it does not error out but does not give me the desired
results.

Thank you!


Jeff Boyce said:
If you copied that SELECT statement and pasted it into a new (empty)
query's
SQL window, will Access run it?

If a little confused by your use of "Like "*" & xxxxx -- this will only
work
if what you are comparing to ENDS WITH "xxxxx" -- is that what it's
supposed
to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 2 different variations of an Access query which is referencing
fields
on an open form for criteria. Unfortunately the statement with the IIF
greater then or less then operators in the HAVING statement does not
return
results and does not error out...

The following statement works:

SELECT WFP.BucketS, WFP.Bucket, Count(WFP.Bucket) AS CountOfBucket
FROM WFP
GROUP BY WFP.BucketS, WFP.Bucket, WFP.LOB, WFP.Lead1, WFP.ConCode
HAVING (((WFP.LOB) Like "*" & [Forms]![Overview]![Indicator1]) AND
((WFP.Lead1) Like "*" & [Forms]![Overview]![Indicator2]) AND
((WF_Part1.CompanyCodeID)<2000)));



The following statement with an IIF in the HAVING criteria statement of
ConCode does not return results with the greater then and less then
operators
but if I remove the > or < operators it seems to work:

SELECT WFP.BucketS, WFP.Bucket, Count(WFP.Bucket) AS CountOfBucket
FROM WFP
GROUP BY WFP.BucketS, WFP.Bucket, WFP.LOB, WFP.Lead1, WFP.ConCode
HAVING (((WFP.LOB) Like "*" & [Forms]![Overview]![Indicator1]) AND
((WFP.Lead1) Like "*" & [Forms]![Overview]![Indicator2]) AND
((WFP.ConCode)
Like
IIf([Forms]![Overview]![tgl_ChartsInFocus]=1,(WFP.ConCode)<2000,(WFP.ConCode)>2000)));


?????
I have tried searching for similar issues in the forum but have not had
any
luck.

Thank you in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

I'm not following ...

How are you (or Access) supposed to know that

(WFP.CoCode < 2000)

means

Region 1

?

If your table includes [Region], couldn't you just query for [Region] = 1?

Regards

Jeff Boyce
Microsoft Office/Access MVP


AccessARS said:
I have a form that provides users data in two different parts one side is
the
data for Regions 1 and 2 in a tabular format and the other is the
aggregate
charts which allows the user to toggle between Region 1 and 2.

By default, the data behind the tabular form and subsequent queries run
and
display all leaders (considering that the fields [Indicator1] and
[indicator2] identify leadership and are Null by default) for both regions
1
and 2.

The query in question is driving one of several charts on display on the
Chart side, which also run all leaders ( i.e (((WFP.LOB) Like "*" &
[Forms]![Overview]![Indicator1]) AND...[Indicator2])..) but only focus on
region 1 by default. The statement in question is to respond to a toggle
button on top of the charts that will allow the user to select between
region
"1" and "2" at which time the charts are refreshed/requeried to display
the
appropriate breakdown. Therefore in the query is retreiving Region "1"
identified as [WFP].[CoCode]<2000 (Less Then 2000) and region "2"
identified
as [WFP].[CoCode]>2000 (Greater Then 2000).
i.e
IIf([Forms]![Overview]![tgl_ChartsInFocus]=1,(WFP.ConCode)<2000,(WFP.ConCode)>2000...


I hope this helps..thanks again.

Jeff Boyce said:
Your IIF() statement appears to tell Access to calculate

(WFP.ConCode)<2000

when the test condition = 1, or otherwise, to calculate

(WFP.ConCode)>2000

You've described "how" you're trying to do something. I'm still not
tracking on WHAT that something is.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP



AccessARS said:
Q1. If you copied that SELECT statement and pasted it into a new
(empty)
query's SQL window, will Access run it?

A1. YES

Q2. I'm a little confused by your use of "Like "*" & xxxxx -- this
will
only work if what you are comparing to ENDS WITH "xxxxx" -- is that
what
it's
supposed to do?

A2. The Like "*" & [Forms]![Overview]![Indicator2] statement give me
flexiblity to returns ALL records if [Indicator2] IsNull or only the
value
of
[Indicator2] when it is populated.


The statements below are the SQL string from an Access Query originally
built in Access Query Designer object. They have been working fine
with
the
Like "*" & ... expression until I added the IIF statement in question.
Like
I stated below it does not error out but does not give me the desired
results.

Thank you!


:

If you copied that SELECT statement and pasted it into a new (empty)
query's
SQL window, will Access run it?

If a little confused by your use of "Like "*" & xxxxx -- this will
only
work
if what you are comparing to ENDS WITH "xxxxx" -- is that what it's
supposed
to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 2 different variations of an Access query which is referencing
fields
on an open form for criteria. Unfortunately the statement with the
IIF
greater then or less then operators in the HAVING statement does not
return
results and does not error out...

The following statement works:

SELECT WFP.BucketS, WFP.Bucket, Count(WFP.Bucket) AS CountOfBucket
FROM WFP
GROUP BY WFP.BucketS, WFP.Bucket, WFP.LOB, WFP.Lead1, WFP.ConCode
HAVING (((WFP.LOB) Like "*" & [Forms]![Overview]![Indicator1]) AND
((WFP.Lead1) Like "*" & [Forms]![Overview]![Indicator2]) AND
((WF_Part1.CompanyCodeID)<2000)));



The following statement with an IIF in the HAVING criteria statement
of
ConCode does not return results with the greater then and less then
operators
but if I remove the > or < operators it seems to work:

SELECT WFP.BucketS, WFP.Bucket, Count(WFP.Bucket) AS CountOfBucket
FROM WFP
GROUP BY WFP.BucketS, WFP.Bucket, WFP.LOB, WFP.Lead1, WFP.ConCode
HAVING (((WFP.LOB) Like "*" & [Forms]![Overview]![Indicator1]) AND
((WFP.Lead1) Like "*" & [Forms]![Overview]![Indicator2]) AND
((WFP.ConCode)
Like
IIf([Forms]![Overview]![tgl_ChartsInFocus]=1,(WFP.ConCode)<2000,(WFP.ConCode)>2000)));


?????
I have tried searching for similar issues in the forum but have not
had
any
luck.

Thank you in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IIF in Query 2
Using IIF statement in Criteria 3
Trying to find Nulls in an IIF statement 12
SumIf: use of greater or less than operators in Criteria 2
Problem with conditional criteria in query. 2
selecting null with an iif expression in criteria access 2k3 13
IIf Statement in Subform Query 1
Dates and Iif function in query criteria 4

Top