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