calculating time difference in access

[ianvanniekerk]

i've got 2 textboxes in which an enter time (the time a client enters the workshop) is automatically updated when the ADD NEW button is clicked, and the 2nd textbox is updated when the client leaves the workshop. What i need is the difference in time (how long the customer was in the workshop) from entering till leaving. Can this be done in MS Access???

 

[ivoef]

Hi,

try this: when your visitor leaves workshop (you should be notified somehow) use the following formula

dim dtDiff as date
dtDiff = datediff("m",Form![txtStartTime],Now)

where txtStartTime is date and time when a customer entered workshop.

Ivo

 

[WildHare]

Ivo has you on the right track, but the dtDIFF variable should be declared as a NUMBER, not a DATE. The temporal interval between any two date/time guys, from seconds to years, is a NUMBER (of somethings) not a date itself.

Here are the DateDiff strings for time intervals:

s - Seconds
n - miNutes
h - Hours
d - Days
w - Weeks
m - Months
q - Quarters
yyyy - Years

Don't ask me why you need FOUR y's for the year guy.

The datediff calculation will give you an accurate count of the number of "things" that have elapsed between the two date/time guys. It's up to you to determine how to modify that afterwards. FOr example, if you are only getting a "time" guy from people, you need to remember that without a DATE part, any TIME guy is set to TODAY. So if you enter 9:00AM as a start, and 1:15AM as an END, and you mean 1:15 on the NEXT day, you need to catch this, because the DATE part of the 1:15AM will be set to TODAY (e.g. earlier than the start time), so you need to add 24 hours to any TIME guy that is actually on the next DAY.

This is a common error that many people get messed up on.

Your input guys don't need to be declared or formatted anyway special - just make them text boxes and INSTRUCT the user how to enter times:

9:00 AM
1:30 PM

DateTime calculations are EASY if you don't try to overcomplicate them with all sorts of nonsense. Just remember the rules - a DATE part entered without TIME is midnight, and a TIME part entered without a date is TODAY.

Jim
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm