Date Query not showing todays entries

C

callieandmark

I have a simple query which requires a Start Date and End Date to
search through orders. If i put in say, 1st August to 7th August, i
only get back the orders between the 1st and 6th and not the 7th. To
get the 7th to display I need to put between 1st and 8th !

Any ideas ?
 
D

Douglas J. Steele

How are you populating the date field in question? If you're using the Now()
function, it includes time as well as date.

Access stores date/times as 8 byte floating point numbers, where the integer
portion represents the date as the number of days relative to 30 Dec, 1899,
and the decimal portion represents the time as a fraction of a day.

07 Aug, 2005 is 38571. If you've got, say, 07 Aug, 2005 at 06:00:00, that's
38571.25, which is not less than 38571.

If you don't need the time in that field, use the Date() function to
populate it rather than the Now() function.

If you do need to store both date and time, you can extract only the date
portion using the DateValue function. However, it's more efficient to simply
to increment the End Date as you've been doing.
 
K

Ken Sheridan

As Douglas has explained this is almost certainly due to your date/time
values containing non-zero times of day. As you’ve found the solution is to
add a day to the end date of the range, but rather than having to remember to
do this when entering parameter values incorporate it in the query, e.g.

PARAMETERS
[Start Date:] DATETIME,[End Date:] DATETIME;
SELECT *
FROM MyTable
WHERE MyDate >= [Start Date:]
AND MyDate < [End Date:] + 1;

Don't be tempted to use:

PARAMETERS
[Start Date:] DATETIME,[End Date:] DATETIME;
SELECT *
FROM MyTable
WHERE MyDate BETWEEN [Start Date:] AND [End Date] + 1;

If you happened to have a value in the MyDate column at midnight at the
start of the day following [End Date:] then that row would be erroneously
returned by the latter query, whereas the former would only return rows with
values up to the final possible moment before midnight. Midnight is
normally regarded as the start of the day, not the end and is thus expressed
in Access as a date value of the day just starting with a zero time of day
(unlike my microwave oven, which perversely thinks the time between midnight
and 1 AM is hour 24 of the previous day!)
 
Top