troubleshooting a Between ... And ... query with dates

[Linda]

I've created a simple query to get the number of hours a
person worked between two dates. For each employee, it
returns the name of each project the employee worked on
between the dates the user enters.

SELECT DISTINCTROW EMPLOYEES.Name, Sum(TimeWorked.Hours)
AS [Sum Of HOURS]
FROM EMPLOYEES INNER JOIN (Categories INNER JOIN (PROJECTS
INNER JOIN TimeWorked ON PROJECTS.ProjectID =
TimeWorked.ProjectID) ON Categories.CategoryID =
PROJECTS.CategoryID) ON EMPLOYEES.EmployeeID =
TimeWorked.EmployeeID
WHERE (((TimeWorked.Date) Between [Beginning Date] And
[End Date]))
GROUP BY EMPLOYEES.Name;

The problem? This works for some dates, and not for
others. If I enter the [Beginning Date] 10/23/2003 and
the [End Date] 10/24/2003, I get only hours worked on
10/23/2003, except for one employee, for whom I get the
10/24 hours reported for 1 of 4 projects she worked on
that day. However, if my [Beginning Date] is 10/24/2003
and my [End Date] is 10/25/2003, I get an accurate report.

I've played around with various dates and can't figure out
a patter. Ideas? thanks.

 

[John Vinson]

If I enter the [Beginning Date] 10/23/2003 and
the [End Date] 10/24/2003, I get only hours worked on
10/23/2003

This arises from the way Access stores dates: as a Double Float
number, a count of days and fractions of a day (times) since midnight,
December 30, 1899. An [End Date] of 10/24/2003 corresponds to midnight
at the beginning of that day; an entry in your table or #10/24/2003
11:30am# is NOT within that range!

I'd suggest using a criterion

= DateValue([Beginning Date]) AND < DateAdd("d", 1, DateValue([End Date]))

The DateValue will truncate any date and time value to the pure date;
and the DateAdd will ensure that times up to 11:59:59pm are counted as
part of the end date.