[AccessD] How to Deal with Time Durations

Hi Bryan

> 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.

First, you should always convert any input from a textbox to a
date/time value with CDate() before using it in your code. If you have
formatted the texbox to date/time, Access will have validated the
input; if not, use IsDate() to validate before using CDate().
Then, the only situation where you will need a string representation
of a date/time is when writing code for SQL or filters where you
always will have to format to US format - this has been dealt with
several times here. 

> 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.

As you, in this case, cannot use a fixed format for the texboxes, this
format will be that of the user's settings for date/time in Windows.

> 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.

Another possibility would be to let the user select a date/time format
if he/she wants another than the settings from Windows and store the
selection as a property of the user. This requires, of course, that
the users do login to your app.

/gustav