Between two dates and times

[Guest]

Hi,

I want to select records between two dates by am not getting the final date.
I am sure it has something to do with Access defaulkts of 12.00am in date
calcs and need a solution.

My data set example is as follows

Date: 22/5/06 6.02 PM
Date: 23/5/06 8.02 PM
Date: 24/5/06 5.02 PM
Date: 25/5/06 6.02 PM

If I do a query of "BETWEEN 22/5/06 AND 24/5/06" it return on the first two
records.

How can I get that date query to return the full 3 records. Essentially I
want it to return between 12.01am of 22/5 to 11.59pm of 24/5.

Your assisatnce is appreciated.

 

[fredg]

Hi,

I want to select records between two dates by am not getting the final date.
I am sure it has something to do with Access defaulkts of 12.00am in date
calcs and need a solution.

My data set example is as follows

Date: 22/5/06 6.02 PM
Date: 23/5/06 8.02 PM
Date: 24/5/06 5.02 PM
Date: 25/5/06 6.02 PM

If I do a query of "BETWEEN 22/5/06 AND 24/5/06" it return on the first two
records.

How can I get that date query to return the full 3 records. Essentially I
want it to return between 12.01am of 22/5 to 11.59pm of 24/5.

Your assisatnce is appreciated.

Your Date Field includes a Time value.
Therefore any data past 00:00:00 AM of the [EndDate] is not within the
Between [FromDate] And [EndDate] criteria.
Easiest work-around is to just add one day manually to the [EndDate]
when prompted, i.e. instead of entering 24/5/2006 enter 25/5/2006.

A better work-around would be to set
[FromDate]
and
[EndDate]
and their expected Datatype as query parameters in the Query Parameter
Dialog (Click Query + Parameter).
Then Change your criteria to:
Between [FromDate] and ([EndDate] + 1)

The best solution, if the Time of day is not necessary, is to run an
Update Query to remove the time value from the date:

Update YourTable Set YourTable.[DateField] = int([DateField]);

Then make sure the time is not included in any new entries, i.e. use
Date() instead of Now().