[AccessD] Date-Time Durations yet again

Arthur Fuller artful at rogers.com
Fri Nov 25 13:47:18 CST 2005


I have scouted the archives and didn't find what I need, so I will risk your
accusations that I don't know how to search efficiently, and ask the
question....
I am working on an inherited app one of whose tables contains StartTime,
StopTime and TravelTime, all of which are defined as datetime fields. The
first two definitions I can live with; the last I deem idiotic.
Q1: It has so far never occurred that StopTime occurs on the day following
StartTime, so given the current data this is a mere subtraction. But I am
unsure how to do it. What I wish to store is the number of minutes between
StartTime and StopTime (and even better if the formula can anticipate that
StopDay is > StartDay -- even though this has never happened).
It seems to me that TravelTime should not be a DateTime field but rather an
integer equivalent to the number of minutes. Thus, SubTotal on the given
line equals StopTime - StartTime (expressed as minutes) + TravelTime
(integer). So SubTotal (a concept if not an actual field) is expressed in
minutes, which in turn are divided by BillingRate (expressed in hours, with
a floor; i.e. minimum time = x hours, anything less gets bumped up to x;
IOW, client bills for 4 hours (half a day) minimum.
I can figure out these details once I have an accurate method of determining
the number of minutes between Start and Stop. (There may be additional
wrinkles, such as those imposed by cell-phone companies wherein the charge
is per minute not per second, but that is trivial once I have the basics in
place.)
Q2: In the inherited app, travel time is recorded as a DateTime field.
Bummer! Yeah, well, that is one reason among several why they called me in.
So, given a DateTime value of 12:30AM, I take this to mean that 30 minutes
of travel time are to be added to the Subtotal. 
Thus another question: how to efficiently derive an integer of 30 from the
datetime value 12:30am. I suppose I can grab the minutes portion; I am just
asking if anyone can see any glaring errors in this assumption. (I know how
to do this in SQL but I don't know how to do it in Access.)
TIA,
Arthur




More information about the AccessD mailing list