By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,059 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Less Than / Greater Than in query criteria

P: n/a
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

Sep 26 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Sep 26 '07 #2

P: n/a
On Sep 26, 9:51 am, "Rick Brandt" <rickbran...@hotmail.comwrote:
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!

Sep 26 '07 #3

P: n/a
I tried your suggestion and it works fine, Rick. Thanks for sharing
your knowledge!!

gary b

Sep 26 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.