G
Guest
For a timesheet application, I am using un-normalized Timesheet and Timesheet
Detail tables on a form that shows 2 weeks of data in a grid format. This is
done to simplify the data entry. Rather than forcing the user to enter a
projectnumber, date, and hours, each row represents a given project, and the
user can cursor to one of 14 columns (DayHours1, DayHours2, etc.)and enter
the hours worked that day. There are 14 StatusID fields as well.
The user, when the timesheet is complete, posts the data to normalized
tables from which aggregate reporting and billing are done. All of these
functions have been tested thoroughly.
It is unlikely but possible, however, that a user will accidentally post an
incomplete or inaccurate sheet. In this case, I want him or her to
immediately contact the Office Manager so that no further billing is done and
so that the timesheet can be restored from the normalized tables.
Several steps of this process are now debugged. The current step is
generating a syntax error. I started the query in Query Design View on a
single day in which I new test data existed, DayHours9:
UPDATE (TSHistory INNER JOIN Timesheet
ON TSHistory.StaffID = Timesheet.StaffID)
INNER JOIN (TimeRecords INNER JOIN TimeSheetDetail
ON TimeRecords.ProjectNumber = TimeSheetDetail.ProjectNumber)
ON (Timesheet.StaffID = TimeSheetDetail.StaffID)
AND (TSHistory.TSID = TimeRecords.TSID)
SET TimeSheetDetail.DayHours9 = TimeRecords.Hours, TimeSheetDetail.Status9 =
IIf(TimeRecords.StatusID=2,1,TimeRecords.StatusID)
WHERE
((TSHistory.PeriodBeginning)=[Forms]![RestoreTimesheet]![txtPeriodStartDate])
AND ((TSHistory.StaffID)=[Forms]![RestoreTimesheet]![cboStaffID])
AND ((TimeRecords.PeriodDay)=9));
This runs and updates the TimeRecordsDetail table as expected.
I brought the SQL into my code and built an SQL string in steps, replacing
references that Jet will not recognize on executing db.Execute, for example,
[Forms]![RestoreTimesheet]![cboStaffID]
became
Forms("RestoreTimesheet").Controls("cboStaffID")
I then ran the code, using:
db.Execute strSQL, dbFailOnError
It executed successfully.
Now that I've changed the code to loop for all 14 sets of fields. The first
iteration of strSQL as printed to the Immediate Window is:
UPDATE (TSHistory INNER JOIN Timesheet
ON TSHistory.StaffID =Timesheet.StaffID)
INNER JOIN (TimeRecords INNER JOIN TimeSheetDetail
ON TimeRecords.ProjectNumber = TimeSheetDetail.ProjectNumber)
ON (Timesheet.StaffID = TimeSheetDetail.StaffID)
AND (TSHistory.TSID = TimeRecords.TSID)
SET TimeSheetDetail.DayHours1= TimeRecords.Hours, TimeSheetDetail.Status1=
IIf(TimeRecords.StatusID=2,1,TimeRecords.StatusID)
WHERE (((TSHistory.PeriodBeginning)=#12/31/2006#)
AND ((TSHistory.StaffID)=8) AND ((TimeRecords.PeriodDay)=1)
This generates a "Missing ), ], or Item in query expression" error on the
WHERE clause at:
(((TSHistory.PeriodBeginning) = #12/31/2006#)
Can anyone figure out what's happening? I've stared at it for a half hour
without understanding.
Thank you.
Sprinks
Detail tables on a form that shows 2 weeks of data in a grid format. This is
done to simplify the data entry. Rather than forcing the user to enter a
projectnumber, date, and hours, each row represents a given project, and the
user can cursor to one of 14 columns (DayHours1, DayHours2, etc.)and enter
the hours worked that day. There are 14 StatusID fields as well.
The user, when the timesheet is complete, posts the data to normalized
tables from which aggregate reporting and billing are done. All of these
functions have been tested thoroughly.
It is unlikely but possible, however, that a user will accidentally post an
incomplete or inaccurate sheet. In this case, I want him or her to
immediately contact the Office Manager so that no further billing is done and
so that the timesheet can be restored from the normalized tables.
Several steps of this process are now debugged. The current step is
generating a syntax error. I started the query in Query Design View on a
single day in which I new test data existed, DayHours9:
UPDATE (TSHistory INNER JOIN Timesheet
ON TSHistory.StaffID = Timesheet.StaffID)
INNER JOIN (TimeRecords INNER JOIN TimeSheetDetail
ON TimeRecords.ProjectNumber = TimeSheetDetail.ProjectNumber)
ON (Timesheet.StaffID = TimeSheetDetail.StaffID)
AND (TSHistory.TSID = TimeRecords.TSID)
SET TimeSheetDetail.DayHours9 = TimeRecords.Hours, TimeSheetDetail.Status9 =
IIf(TimeRecords.StatusID=2,1,TimeRecords.StatusID)
WHERE
((TSHistory.PeriodBeginning)=[Forms]![RestoreTimesheet]![txtPeriodStartDate])
AND ((TSHistory.StaffID)=[Forms]![RestoreTimesheet]![cboStaffID])
AND ((TimeRecords.PeriodDay)=9));
This runs and updates the TimeRecordsDetail table as expected.
I brought the SQL into my code and built an SQL string in steps, replacing
references that Jet will not recognize on executing db.Execute, for example,
[Forms]![RestoreTimesheet]![cboStaffID]
became
Forms("RestoreTimesheet").Controls("cboStaffID")
I then ran the code, using:
db.Execute strSQL, dbFailOnError
It executed successfully.
Now that I've changed the code to loop for all 14 sets of fields. The first
iteration of strSQL as printed to the Immediate Window is:
UPDATE (TSHistory INNER JOIN Timesheet
ON TSHistory.StaffID =Timesheet.StaffID)
INNER JOIN (TimeRecords INNER JOIN TimeSheetDetail
ON TimeRecords.ProjectNumber = TimeSheetDetail.ProjectNumber)
ON (Timesheet.StaffID = TimeSheetDetail.StaffID)
AND (TSHistory.TSID = TimeRecords.TSID)
SET TimeSheetDetail.DayHours1= TimeRecords.Hours, TimeSheetDetail.Status1=
IIf(TimeRecords.StatusID=2,1,TimeRecords.StatusID)
WHERE (((TSHistory.PeriodBeginning)=#12/31/2006#)
AND ((TSHistory.StaffID)=8) AND ((TimeRecords.PeriodDay)=1)
This generates a "Missing ), ], or Item in query expression" error on the
WHERE clause at:
(((TSHistory.PeriodBeginning) = #12/31/2006#)
Can anyone figure out what's happening? I've stared at it for a half hour
without understanding.
Thank you.
Sprinks
