Compare Values plus an adjustment factor

[John Steele]

In an MS ACCESS SQL "SELECT" Is there anyway to add/subtract and
"adjustment" value to the value in the database?

For example is want to compare a date/time value with a date/time value
stored in the database but I want the comparison to be to an "adjusted"
value (the code is in PERL):
NB for other process reasons
1. the date and time are stored in separate fields so we have to concatenate
them
2. the time is stored in decimal (i.e. 21:30 is stored as 21.50, 09:45 as
09.75, etc)
--------------------------
So we have tried variations of "sd_date & sd_time_out-0.05" thus :
....................................................................................^nnnn........

$SqlStatement = "SELECT DISTINCT sm_location_id, sm_customer_id, sd_id,
sd_date, sd_time_in, sd_time_out
FROM Schedule_Master, Schedule_Detail
WHERE sm_location_id <> '$locationID' AND
(sd_master = sm_id AND sd_employeeid = '$sd_employeeid' AND
(($sd_datetime_in BETWEEN sd_date & sd_time_in+0.05 AND sd_date &
sd_time_out-0.05) OR ($sd_datetime_out BETWEEN sd_date & sd_time_in+0.05 AND
sd_date & sd_time_out-0.05))";

This SQL occurs once for each line item in the current schedule and is
pretty quick as we only get data from the SQL when there is a "hit" so we
only have to process the conflicts. This appears to works sometimes but the
SQL fails under some other circumstances, I suspect when the time to be
tested is 0.00 or something and the adjustment results in a negative value.

We are doing this to catch conflicts in scheduling of the same employee at
two locations if the start and end times overlap by 3 minutes. Thus far we
have accomplished this by making the overlap adjustment in value for
"$sd_datetime_in" and "$sd_datetime_out". But while this works most of the
time there are time situations where we do not get a "hit" using this
approach. So we're looking to see if we can leave the input comparison alone
and test against an "adjusted" stored value.

We could accomplish this by reading each In/Out time for every employee and
comparing it but that would really slow things down. The current approach is
quick as we only get data from the SQL when there is a "hit."

Any guidance?

 

[Michel Walsh]

Hi,


Store the date and time is a single field. (DateValue + TimeValue /
24.0) would produce a date_time value, assuming the date is after the 30th
December 1899 and the TimeValue is in hour and decimal of hour.


Hoping it may help,
Vanderghast, Access MVP

 

[John Steele]

Well yes. But the database is already structured this way and there are 10s
of thousands of records in this format. These fields are used by a dozen or
so modules so changing would be a significant undertaking at this point.