Question about "Between" stmt in query criteria 1

[KerryL]

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.)

 

[Golom]

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

Between CDate([forms]![frmRptCriteria]![txtBeginDate]) And
        CDate([Forms]![frmRptCriteria]![txtEndDate])

 

[KerryL]

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.