Less Than / Greater Than in query criteria

[sfrvn]

I have searched high and low and cannot find an answer to my problem.
So now I turn to the collective genius of this newsgroup.

Over-simplified examples
This query criteria for field [recdate] works:
IIf(1=1,#3/12/2007#,#3/12/2007#)
1=1 will be replaced by actual 'test'; I just want to make sure
expression evaluates 'true'
Query returns all records where [recdate] field equals
3/12/2007date

This query criteria for field [recdate] DOES NOT work:
IIf(1=1,<=#3/12/2007#,>=#3/12/2007#)
1=1 will be replaced by actual 'test'; I just want to make sure
expression evaluates 'true'
I *expect* all records where the value for field [recdate] is
equal to or less than 3/12/2007

I get NO records!

I have tried thousands (well... at least hundreds) of syntax
variations and I cannot get this to work. *ANY* operator symbol (>, <
or =) kills the query. No error... just no records. I'm ready for a
beer.

Would one of you please point out what I am doing incorrectly. Your
help would be very much appreciated!

gary b

[Rick Brandt]

sf***@earthlink.net wrote:

I have searched high and low and cannot find an answer to my problem.
So now I turn to the collective genius of this newsgroup.

Over-simplified examples
This query criteria for field [recdate] works:
IIf(1=1,#3/12/2007#,#3/12/2007#)
1=1 will be replaced by actual 'test'; I just want to make sure
expression evaluates 'true'
Query returns all records where [recdate] field equals
3/12/2007date

This query criteria for field [recdate] DOES NOT work:
IIf(1=1,<=#3/12/2007#,>=#3/12/2007#)
1=1 will be replaced by actual 'test'; I just want to make sure
expression evaluates 'true'
I *expect* all records where the value for field [recdate] is
equal to or less than 3/12/2007

I get NO records!

I have tried thousands (well... at least hundreds) of syntax
variations and I cannot get this to work. *ANY* operator symbol (>, <
or =) kills the query. No error... just no records. I'm ready for a
beer.

Would one of you please point out what I am doing incorrectly. Your
help would be very much appreciated!

gary b

You cannot conditionally change your comparison operator like that. You
need to use an OR criteria like...

SELECT...
WHERE (1=1 AND SomeField <= #3/12/2007#)
OR (1<>1 AND SomeField >= #3/12/2007#)
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[sfrvn]

On Sep 26, 9:51 am, "Rick Brandt" <[e-mail]:

You cannot conditionally change your comparison operator like that. You
need to use an OR criteria like...

SELECT...
WHERE (1=1 AND SomeField <= #3/12/2007#)
OR (1<>1 AND SomeField >= #3/12/2007#)

Thanks, Rick! That approach never occurred to me. I will give it a
try and post results. Please note that I found no similar info in any
of my newsgroup searches on this topic. Maybe your information will
help someone else, too.

Thanks, again!

[sfrvn]

I tried your suggestion and it works fine, Rick. Thanks for sharing
your knowledge!!

gary b