"Between...and" operator problem

When I run a query using the "Between...and" operator with dates, it returns
the correct "Beginning Month" but one month short of the "Ending Month"
specified. For Jan to Dec 2008 specified in the data input form it returns
Jan to Nov 2008. This happens for any range of months. Is there a property
option I can't find?

Post the SQL of your query by opening in design view, click on VIEW - SQL
View, highlight all, copy, and paste in a post.
--
Build a little, test a little.


"Big Point" wrote:

When I run a query using the "Between...and" operator with dates, it returns
the correct "Beginning Month" but one month short of the "Ending Month"
specified. For Jan to Dec 2008 specified in the data input form it returns
Jan to Nov 2008. This happens for any range of months. Is there a property
option I can't find?

On Mon, 5 Oct 2009 07:20:02 -0700, Big Point wrote:

When I run a query using the "Between...and" operator with dates, it returns
the correct "Beginning Month" but one month short of the "Ending Month"
specified. For Jan to Dec 2008 specified in the data input form it returns
Jan to Nov 2008. This happens for any range of months. Is there a property
option I can't find?

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().
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Mon, 5 Oct 2009 07:20:02 -0700, Big Point
wrote:

When I run a query using the "Between...and" operator with dates, it returns
the correct "Beginning Month" but one month short of the "Ending Month"
specified. For Jan to Dec 2008 specified in the data input form it returns
Jan to Nov 2008. This happens for any range of months. Is there a property
option I can't find?

Correct the error in your query criteria.

For help doing so please post the criteria that you are using; open the query
in design view, select View... SQL, and post the SQL text here.
--

John W. Vinson [MVP]