Query/Date Values

[Sprinks]

I'm working on a Timesheet application. My initial data tables had the
following structure:

TSHistory (one record per 2-week timesheet per employee) One Side
----------------------------------------------------------------------------------
TSID AutoNumber (PK)
PeriodBeginning Date/Time
StaffID Integer (FK to Staff)

TimeRecords (Many Side)
---------------------------------
TimeRecordID AutoNumber (PK)
TSID Integer (FK to TSHistory)
ProjectNumber Integer (FK to Projects)
WorkDate Date/Time
Hours Integer
StatusID Integer (FK to Status)
InvoiceDate Date/Time

I realized that this structure is not normalized, and proposed deleting
WorkDate from the detail table and replacing it with a PeriodDay integer
field, which would represent the day of each 14-day period, from which the
WorkDate can easily be calculated by:

[PeriodBeginning] + [PeriodDay] - 1

However, in making a change to an existing working query, what I thought
would be an equivalent doesn't work. Can anyone tell me why?

The two queries differ only in the WHERE clause, and both get their starting
and ending values from a form where the user enters the period of interest.

Thank you.

‘ This works
SELECT Staff.LName & ", " & [FName] AS StaffName, Staff.LName, Staff.FName,
Len(Projects.ProjectNumber) AS L, TimeRecords.ProjectNumber,
Projects.Project, Sum(TimeRecords.Hours) AS SumOfHours
FROM ((StaffRoles INNER JOIN Staff ON StaffRoles.StaffRoleID =
Staff.StaffRoleID) INNER JOIN TSHistory ON Staff.StaffID = TSHistory.StaffID)
INNER JOIN (Projects INNER JOIN TimeRecords ON Projects.ProjectNumber =
TimeRecords.ProjectNumber) ON TSHistory.TSID = TimeRecords.TSID
WHERE (((TimeRecords.WorkDate) Between
[Forms]![MonthlyActivityReportSelectionCriteria]![txtBegin] And
[Forms]![MonthlyActivityReportSelectionCriteria]![txtEnd]))
GROUP BY Staff.LName & ", " & [FName], Staff.LName, Staff.FName,
Len(Projects.ProjectNumber), TimeRecords.ProjectNumber, Projects.Project
ORDER BY Len(Projects.ProjectNumber) DESC;


‘ This doesn’t work
SELECT Staff.LName & ", " & [FName] AS StaffName, Staff.LName, Staff.FName,
Len(Projects.ProjectNumber) AS L, TimeRecords.ProjectNumber,
Projects.Project, Sum(TimeRecords.Hours) AS SumOfHours
FROM ((StaffRoles INNER JOIN Staff ON StaffRoles.StaffRoleID =
Staff.StaffRoleID) INNER JOIN TSHistory ON Staff.StaffID = TSHistory.StaffID)
INNER JOIN (Projects INNER JOIN TimeRecords ON Projects.ProjectNumber =
TimeRecords.ProjectNumber) ON TSHistory.TSID = TimeRecords.TSID
WHERE (([TSHistory].[PeriodBeginning] + [TimeRecords].[PeriodDay]-1) Between
[Forms]![MonthlyActivityReportSelectionCriteria]![txtBegin] And
[Forms]![MonthlyActivityReportSelectionCriteria]![txtEnd])
GROUP BY Staff.LName & ", " & [FName], Staff.LName, Staff.FName,
Len(Projects.ProjectNumber), TimeRecords.ProjectNumber, Projects.Project
ORDER BY Len(Projects.ProjectNumber) DESC;