473,883 Members | 1,408 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Less Than / Greater Than in query criteria

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
3 49030
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
On Sep 26, 9:51 am, "Rick Brandt" <rickbran...@ho tmail.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
I tried your suggestion and it works fine, Rick. Thanks for sharing
your knowledge!!

gary b

Sep 26 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
3799
by: keliie | last post by:
Hello Just a quick question that I've spent a few hours trying to solve with no luck (although one would think this should be fairly easy). I have a form with a subform. The subform is based on a query. The criteria for the query is based on the user's input in the main form. One of the user inputs is a field called "Vendor_Name" (in the form of a combo box). I would like the query to run for all "Vendor_Name" if the user leaves the...
3
9692
by: stevecat | last post by:
Hi there, I have created a form, "search" and a query "search_product". The query returns the product information based upon the criteria for three of the fields, author, title or isbn. The field names on the search form are search_title, search_author and search_isbn. The string entered in the query criteria is: Like !! This works perfectly. But now I need to take the search form and make it a subform in a form called "main". I edited...
4
3521
waynetheengineer
by: waynetheengineer | last post by:
Hi, I was wondering if anyone had any suggestions on my database: I have a form that accepts user input into a single text box. When the OK button is hit, a query should search for all records that contain the user inputted text for a specific field in the query's criteria section. I want the criteria to search for any matching part of the word, for example: If the user enters in the text box: rabbit The query should return all...
3
2289
by: ericargent | last post by:
Hi I'm using Acces 2003 I have Query where the several parameters for the criteria are supplied from a form. One parameter source is a combo box. What I am trying to do is if: An item is selected on the combo box then that value is used as the criteria If user does not want the contents of the combo box used, then Query should ignore that field. I have tried to do this with an iif statement in the query criteria and a control box on...
6
6310
craigfr
by: craigfr | last post by:
I am making a graph comparing last year's defect data with YTD defect data. Our fiscal year starts Nov.1 and ends Oct.31. To get the YTD, I started used a simple date serial criteria: Between DateSerial(Year(Date())-1,11,1) And Date() This works, but only for 10 months out of the year. If your'e in December, it will calculate the YTD from last year's November to the current December, not the last November. My solution was to make an...
2
3952
by: cmartin1986 | last post by:
First of all I want to thank all of you that have helped me in the past this is an awesome fourm. My problem today is I have a database that builds charts that are viewed by a large group every friday morning. I am building six different charts that all have query criteria of between two dates right now I am going and updating the charts manually every week. The dates these charts work on is always friday to thursday every week. Is there...
4
1618
by: tbeers | last post by:
Is there a method to pass along a criteria argument directly to the query criteria rather than through filtering a form or report? In other words, I would like to click a "print" button and in the "on click" procedure pass along the criteria to the query to generate this report. Then I wouldn't have to create so mahy different queries. I know that I can take a control value to use in the criteria box in the query. But I would like to be...
8
2489
by: limperger | last post by:
Hello everyone! First and foremost, my apologies for the title of the post. It is not very clarifying of what the problem is about, but I didn't know how to put it... My problem is as follows: I have a parameter query consisting of 2 fields (month and year). The parameter prompts the user to enter the month and the year, that is, . When both are entered, there is no problem. Nevertheless, users may want only to enter the year, so as to...
9
18134
ajhayes
by: ajhayes | last post by:
Hello everyone, This is my first time posting here and I'm hoping someone can help me out. I'm a relative newbie to Access and am pretty much learning as I go along, so please bear with me. Here is what I am trying to do. I'm building a simple database that will allow our engineering staff to enter in the items from the inventory warehouse that will be required for a project. Each inventory item has a unique part number, which is...
0
11129
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10734
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9569
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7967
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7119
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5794
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5988
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4213
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.