By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,285 Members | 1,619 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,285 IT Pros & Developers. It's quick & easy.

What Field Type & Function to determine Time

P: 3
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.
Aug 14 '08 #1
Share this Question
Share on Google+
3 Replies


100+
P: 167
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]
Aug 14 '08 #2

P: 3
It worked! Thank you so much!
Aug 14 '08 #3

missinglinq
Expert 2.5K+
P: 3,532
"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)>
Aug 14 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.