[AccessD] How to Deal with Time Durations

What I finally decided to do was store the Start Date and Start Time as
two different fields.
Then Store the End Date and end time as two more fields.

On the view form I calculate the duration using some code from the
VBADH.

On the data entry form, I allow the user to enter either the end
date/time OR the duration. Which ever one the enter, I use the ato
calculate the other one.

I try and avoid converting dates into strings or number if at all
possible. Since I live in Canada my users can, and do enter, dates in US
format(mm/dd/yyyy) UK Format (dd/mm/yyyy) AND ISO format(yyyy-mm-dd). I
try and let Access/VBA deal with the conversions from those formats to
the formats it wants to use internally. And to make sure the date gets
converted properly I ALWAYS, ALWAYS, ALWAYS format my dates to show
letters for the month. That way if it converts 01/02/03 into something
the user didn't mean, they can fix it right away.

Bryan Carbonnell
bryan_carbonnell at cbc.ca

>>> adtp at touchtelindia.net 08-Mar-04 2:23:41 PM >>>
Bryan,

    One option could be to store the date as a date field and time as
string. Relevant query can include a calculated field depicting the time
(in seconds) already lapsed or yet to lapse as compared to the present
time (as returned by the Timer function). This affords remarkable
flexibility in using the data the way any particular situation might
demand.

    If it could be of any help, you could take a look at my sample db
named  AppointmentsAlert  at Rogers Access Library (other developers
section).

    It features an appointments planner. When put into standby mode,
the utility goes into minimized state and keeps scanning the status of
scheduled appointments at specified time intervals.

    As & when an event becomes due, an audio alarm is sounded
accompanied by a pop-up form displaying the current status of various
appointments.

    In the underlying queries, calculated field named MinutesTillSlot
should be of interest to you. Related function named Fn_MinutesTillSlot
could be modified to return seconds instead of minutes.

Regards,
A.D.Tejpal
--------------
  ----- Original Message ----- 
  From: Bryan Carbonnell 
  To: accessd at databaseadvisors.com 
  Sent: Thursday, March 04, 2004 20:14
  Subject: [AccessD] How to Deal with Time Durations


  In a new app that I am creating, of which you will probably hear ALL
  about it over the next few weeks :), I have to keep track of event
  durations.

  Right now I have a field for the start time, and one for the
duration.
  Both are Date/Time fields.

  The durations for the event can last from 1 second all the way up to
  many hours, and possibly even days.

  Should I actually be storing the start and end time and calculating
the
  duration? 

  The duration can be entered as either the actual duration or the
actual
  end time.

  If you have done something like this, how have you handled it?

  Thanks,