I have an application in service (A97) which is a booking system for a modelling agency.
When they add / edit a job I display a subform which lists all models, filtered according to criteria set by the user,
from which they can select the model(s) they want to add to the job.
The subform is bound to a temporary table (atblModelSelectTemp2 ) which contain several boolean fields which act as
flags to indicate whether the model is already on the current job / session, is unavailable or has another booking in
the same timeslot (clash).
www.bestfitsoftware.com.au/pics/editform.jpg
One of the boolean fields, (ThisSession) is set to show all models who are on the session currently being edited.
A *session* is all bookings with the same JobID (Long), RefDate (Date/Time), StartTime (Date/Time) and EndTime
(Date/Time).
The following SQL statement sets the ThisSession field -
UPDATE atblModelSelectTemp2 INNER JOIN tblJobTimes
ON atblModelSelectTemp2.ModelID = tblJobTimes.ModelID
SET atblModelSelectTemp2.JobID = [tblJobTimes].[JobID],
atblModelSelectTemp2.ThisSession = True
WHERE (((tblJobTimes.JobID)=" & Forms!afrmSystem!JobIDCheck & ")
AND ((tblJobTimes.RefDate)=#" &
Format(Forms!frmEditJobTimesAndModels!frmEditJobTi mesAndModelsMultiSelectSubform1.Form!RefDate, "mm-dd-yyyy") & "#)
AND ((tblJobTimes.StartTime)=#" &
Forms!frmEditJobTimesAndModels!frmEditJobTimesAndM odelsMultiSelectSubform1.Form!StartTime & "#)
AND ((tblJobTimes.EndTime)=#" & Forms!frmEditJobTimesAndModels!frmEditJobTimesAndM odelsMultiSelectSubform1.Form!EndTime
& "#));
When I run this on my work PC for a specific job, two records are set, which is correct.
When I run the same statement on the client's PC's, no records were set???
Both PC's are running A97 SR2 and both have had JetSP3 installed.
Both FE and BE files are identical on both PC's.
After some investigating I found that if instead of using a criteria of StartTime = subform!StartTime and EndTime =
subform!EndTime, I use DateAdd to extend the criteria to 1 minute either side of subform!xxxTime, the update would set
the correct two records.
The new SQL is -
UPDATE atblModelSelectTemp2 INNER JOIN tblJobTimes
ON atblModelSelectTemp2.ModelID = tblJobTimes.ModelID
SET atblModelSelectTemp2.JobID = [tblJobTimes].[JobID], atblModelSelectTemp2.ThisSession = True
WHERE (((tblJobTimes.JobID)=" & Forms!afrmSystem!JobIDCheck & ") AND ((tblJobTimes.RefDate)=#" &
Format(Forms!frmEditJobTimesAndModels!frmEditJobTi mesAndModelsMultiSelectSubform1.Form!RefDate, "mm-dd-yyyy") & "#)
AND ((tblJobTimes.StartTime)>=#" & DateAdd("n", -1,
Forms!frmEditJobTimesAndModels!frmEditJobTimesAndM odelsMultiSelectSubform1.Form!StartTime) & "#)
AND ((tblJobTimes.StartTime)<=#" & DateAdd("n", 1,
Forms!frmEditJobTimesAndModels!frmEditJobTimesAndM odelsMultiSelectSubform1.Form!StartTime) & "#)
AND ((tblJobTimes.EndTime)>=#" & DateAdd("n", -1,
Forms!frmEditJobTimesAndModels!frmEditJobTimesAndM odelsMultiSelectSubform1.Form!EndTime) & "#)
AND ((tblJobTimes.EndTime)<=#" & DateAdd("n", 1,
Forms!frmEditJobTimesAndModels!frmEditJobTimesAndM odelsMultiSelectSubform1.Form!EndTime) & "#));
It appears the value of StartTime and EndTime is being evaluated differently on the 2 PC's.
xxxTime = xxxTime is evaluated correctly on my PC returning 2 records.
xxxTime = xxxTime is evaluated incorrectly on the client's PC returning 0 records.
I imagine that as date/times are stored as doubles, that the error is due to a floating point precision error, but why
would the same value be evaluated differently on the 2 PC's when it essentially comparing a stored value against a
stored value?
Wayne Gillespie
Gosford NSW Australia