Between...And Operators

[Guest]

I have a query whose criteria is based on dates on a form:

Between [Forms]![Performance Measure]![txtBeginDate] And
[Forms]![Performance Measure]![txtEndDate]

txtBeginDate = 10/1/06, txtEndDate = 10/31/06

My problem is the query will not include all of the dates from 10/31/06. If
I change txtEndDate to 11/1/06 then all the dates from 10/31/06 will appear.
I thought using the Between...And Operator would include all dates b/w
10/1/06 and 10/31/06 plus include anything dated 10/1/06 or 10/31/06. Was I
wrong?

Any suggestions appreciated. Thank you.

 

[John Spencer]

The problem is that your date field contains a time in addition to the date.
This often happens when someone uses NOW() to populate the field, instead of
using Date() to populate the field.

Since a date by itself is at midnight, any date time that is after midnight
will not be included.

You can try
Between [Forms]![Performance Measure]![txtBeginDate] And DateAdd("d",1,
[Forms]![Performance Measure]![txtEndDate])

or to be really accurate

= [Forms]![Performance Measure]![txtBeginDate] And < DateAdd("d",1,
[Forms]![Performance Measure]![txtEndDate])

You can also use the DateValue function to trim off the time from your
field. The problem here is that this may be noticably slower when it
searches since the DateValue function will get called for every row of data
AND any index on the field will not be used.

Field: DateValue(YourDateField)
Criteria: Between [Forms]![Performance Measure]![txtBeginDate] And
[Forms]![Performance Measure]![txtEndDate]

I have a query whose criteria is based on dates on a form:

Between [Forms]![Performance Measure]![txtBeginDate] And
[Forms]![Performance Measure]![txtEndDate]

txtBeginDate = 10/1/06, txtEndDate = 10/31/06

My problem is the query will not include all of the dates from 10/31/06.
If
I change txtEndDate to 11/1/06 then all the dates from 10/31/06 will
appear.
I thought using the Between...And Operator would include all dates b/w
10/1/06 and 10/31/06 plus include anything dated 10/1/06 or 10/31/06. Was
I
wrong?

Any suggestions appreciated. Thank you.

 

[Guest]

I figured out the problem.

 

[Guest]

My problem is the query will not include all of the dates from 10/31/06.
A datatype of datetime can contain time with the date. A date is a whole
number and the time is a decimal fraction of a day such as 6 AM is 0.25 or a
quarter of a day.
When you use just the date in you criteria it does not accept any date with
time on the highest date as it is greater than the date.
There are several solutions. One is to go one day beyond. Another is to
strip the time fraction off the record like Int([My End Date]) which
leaves a whole number.