Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Question about "Between" stmt in query criteria 1

Status
Not open for further replies.

KerryL

Technical User
Joined
May 7, 2001
Messages
545
Location
US
To filter records based on a date range I'm using the following criteria in a query:

Between [forms]![frmRptCriteria]![txtBeginDate] And [Forms]![frmRptCriteria]![txtEndDate]


However, if the date range is 1/1/07 to 1/31/07, the "Between" statement excludes any records created on 1/1/07 or 1/31/07, finding only those in between.

How can I remedy this? How should I modify the "Between" statement so that the query includes records that equal the begin & end dates plus everything in between?

(I tried "On Or Between..." but the query returned all records.)
 
Between" does include the end points but you may be having the problem if your date fields in the table were populated with "Now()" rather than "Date()".

Internally, dates are doubles with the date before the decimal and the time after it. For example 01/01/31 is the number 39113 but 01/01/31 1:15:22 PM is 39113.552337963 which is greater than the date alone so it won't be included.


The other problem that you may be having is that the text box values are not being explicitly converted to dates so you may try
Code:
Between CDate([forms]![frmRptCriteria]![txtBeginDate]) And
        CDate([Forms]![frmRptCriteria]![txtEndDate])
 
Golom,
You were right. I was using NOW() to populate the EntryDate field, so anything entered on 1/31/07 had an actual date higher than 1/31.

I modified the code by changing NOW() to DATE() and the field is now being populated with only the date, which allows the BETWEEN statement to work fine.

Thank you!


PS - I'm struggling with how to calculate some subset data from this filtered query, so I may post again if I can't figure it out.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top