Gary Kjos
garykjos at gmail.com
Fri Nov 25 14:58:33 CST 2005
Hi Arthur,
I agree with your position on the use of a date/time field for the
travel time. Date/time fields are made for points in time, not
quantities of time.
The statement to use to determine the difference between two date time
fields is DATEDIFF. Unfortunately it doesn't seem to be in the HELP
files anymore, but it still seems to work. You can get info on it
here;
http://www.techonthenet.com/access/functions/date/datediff.php
Basically you would have a statement like this in a query....
TravelTime: DateDiff("n",[EndTime],[StartTime])
The "n" there means to return Minutes from the function. (M means
MONTHS. See the list in that link for all the different operators you
could use.
You could also use the datediff function to solve your second question
by calculating the difference in minutes between the old date/time
based travel time field and the date stored in that field without the
time which you can get with the DATEVALUE statement. So something like
datediff("n",[TravelTime],datevalue([TravelTime])
would give you the number of minutes in travel time. Assuming I don't
have the operands reversed which I have been known to do.
DateValue used to be included in the help files too but I cannot find
it now. Why I keep the A97 help files on my work system.
Good luck straightening it out.
Gary
On 11/25/05, Arthur Fuller <artful at rogers.com> wrote:
> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
Gary Kjos
garykjos at gmail.com