>=[BeginDate] And <=[End Date]

[Iram]

Hello.
I am using Access 2003. When I write a parameter querey with

=[Enter a Begin Date] And <=[Enter an End Date]

as criteria for a Date field, I don't get the desired results.

When I put 6/9/2009 through 6/12/2009 I get records that start with 6/9/2009
but the records end with the last record created on 6/11/2009. I need this
query to pull up every record starting and ending with the date range which
should include 6/12/2009. How can I fix this querey?


Thanks.
Iram/mcp

 

[fredg]

Hello.
I am using Access 2003. When I write a parameter querey with

=[Enter a Begin Date] And <=[Enter an End Date]

as criteria for a Date field, I don't get the desired results.

When I put 6/9/2009 through 6/12/2009 I get records that start with 6/9/2009
but the records end with the last record created on 6/11/2009. I need this
query to pull up every record starting and ending with the date range which
should include 6/12/2009. How can I fix this querey?

Thanks.
Iram/mcp

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 11/24/2005 enter 11/25/2005.

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] = DateValue([DateField]);

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