Date query not giving EndDate

[TSpade]

Hello - using parameter query with Between [StartDate] And [EndDate] to query
an order table. Orders with dates equal to the [EndDate] do not appear in
results.

 

[Ofer Cohen]

Try to convert the parameters to date, and see what will be the resaults

Between CVDate([StartDate]) And CVDate([EndDate])

 

[Allen Browne]

This will be due to the fact that your fields have a time component stored
there.

If you ask for dates between July 1 2006 and July 2 2006, the records that
contain July 2 10AM will not be returned, because that is after midnight on
the closing date.

To work around the problem, ask for records that are less than the next day,
i.e.:

= [StartDate] AND < ([EndDate] + 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello - using parameter query with Between [StartDate] And [EndDate] to
query
an order table. Orders with dates equal to the [EndDate] do not appear in
results.

 

[TSpade]

Thanks for the recommendation, but this still doesn't give me the records for
the last day of the range, I can see the orders with dates of 6/30/06 but
when I use 6/30/06 as the EndDate, those records are not in the query results.
--
Tspade

Try to convert the parameters to date, and see what will be the resaults

Between CVDate([StartDate]) And CVDate([EndDate])
--
Good Luck
BS"D

Hello - using parameter query with Between [StartDate] And [EndDate] to query
an order table. Orders with dates equal to the [EndDate] do not appear in
results.

 

[TSpade]

Access tells me that I have too complex a calculation... do you have another
idea?
--
Tspade

This will be due to the fact that your fields have a time component stored
there.

If you ask for dates between July 1 2006 and July 2 2006, the records that
contain July 2 10AM will not be returned, because that is after midnight on
the closing date.

To work around the problem, ask for records that are less than the next day,
i.e.:

= [StartDate] AND < ([EndDate] + 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello - using parameter query with Between [StartDate] And [EndDate] to
query
an order table. Orders with dates equal to the [EndDate] do not appear in
results.

 

[TSpade]

OK, what works is a combination of the 2 recommendations I received...

Between CVDate([StartDate]) And (CVDate([EndDate])+1)

This gave me the records in the requested range including the records with
Order Date = the EndDate.

Thank you both for your ideas.

 

[Allen Browne]

The message indicates that Access is having trouble understanding your
criteria.

1. Declare your parameters
In query design view, choose Parameters on the Query menu.
Access opens a dialog.
Enter 2 rows:
StartDate Date/Time
EndDate Date/Time

2. Verify that you did include the > sign at the start of the criteria.
(Sometimes it can look like a newsgroup quote, but it is a greater than
sign.)

3. If you are applying this criteria on a calculated date, add CVDate()
around the calculation. Explanation:
http://allenbrowne.com/ser-45.html

4. If the field is not really a date/time field, that could cause the
problem too.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Access tells me that I have too complex a calculation... do you have
another
idea?
--
Tspade

This will be due to the fact that your fields have a time component
stored
there.

If you ask for dates between July 1 2006 and July 2 2006, the records
that
contain July 2 10AM will not be returned, because that is after midnight
on
the closing date.

To work around the problem, ask for records that are less than the next
day,
i.e.:

= [StartDate] AND < ([EndDate] + 1)

Hello - using parameter query with Between [StartDate] And [EndDate] to
query
an order table. Orders with dates equal to the [EndDate] do not appear
in
results.