What Field Type & Function to determine Time

[burtoncn]

Hi,

Just a little background information on the project I am working on!

I am responsible for creating a database to automatically generate travel claims for conference attendees (Approx. 300) using Access 2003. For our staff to accurately determine which meal allowances they are entitled to claim, we have to determine the travel distance and travel time using Google Map (something similar).

For example: an Attendee X has to be at the conference by 11am. It will take X 1hr and 54 minutes to drive to the conference so he should leave by 9:00 to arrive on time. This individual will not be allowed to claim for breakfast.

Now for the questions:

1.What field type do I use to capture the travel time? Date/Time or Text? How should the information be entered? 1:54 minutes, 1 hr 54 m etc. Should I convert the hours and minutes to minutes?

2.What function do we use to show the departure time? The claims should show ideal departure time?

Thanks in advance.

[hjozinovic]

1.What field type do I use to capture the travel time? Date/Time or Text? How should the information be entered? 1:54 minutes, 1 hr 54 m etc. Should I convert the hours and minutes to minutes?

You should use Date/Time field type formatted like this: hh:mm

2.What function do we use to show the departure time? The claims should show ideal departure time?

In a query add a field like this:
[HTML]DepartureTime: Format([ArrivalTime]-[TravelTime];"hh:mm";)[/HTML]

[burtoncn]

It worked! Thank you so much!

[missinglinq]

"Travel time" is not Time as defined by Access! "Travel time" is the difference between one Date/Time value and another Date/Time value, and should be expressed as a Number.

Think about it; what would you do if you wanted to calculate the total "Travel time" for an employee for the conference? You'd multiply the "Travel time" by two. But you cannot multiply a Date/Time value by two!

As to whether to use hours or minutes, either use minutes (my preference) or hours and fraction of hours, i.e.

90 minutes

or

1.5 hours.

Using minutes makes it much easier doing calculations. You can always parse the minutes into hours and minutes , if need be, for display.

Welcome to Bytes!

Linq ;0)>