"Date Diff"ing

[Stephenn]

OK, “DateDiff”ing has always given me fits, but I’ve always managed to figure out how to get what I need by trial and error. Until now. For the first time I need to figure out the difference between Time_In and Time_Out when Time_Out is past midnight. I’ve tried all sorts of things, nothing works. Can some one recommend a good way to count hours going from one calendar date to another? The closest I get is:
Hrs_Worked = DateDiff("hh:nn", Time_In, Time_Out)
Any suggestions?
PS. I'll need to then multiply Hrs_Worked by an hourly rate.
TIA

 

[jatfill]

On the structure end, you may want to consider adding the date to the timein/timeout fields instead of just storing the times. If you do this, the calulation bacomes pretty simple, and you can still display just the times using the Format function. If you do store the dates, you can even just use the expression

Elapsed: ([timeout]-[timein])

To pull the total amount of time...

 

[Pat Hartman]

jatfill, the default interval is days. So, if you just subtract two dates, the answer is returned as days.

Stephenn, use DateDiff() to calculate the difference in minutes. Then convert to hours:minutes by dividing by 60 to get hours, mod the minutes by 60 to get the remaining minutes and concatinate the two results with a colon for display purposes.

 

[Stephenn]

OK, Changing Time_In and Time_Out to general date format helped. It took a while to get it, but I finally got it working right, somewhat. Problem now is that I get a screwy return for Hrs_Worked. Employee worked from 8 am to Midnight. 16 hours, right? Well I’m getting 15.9999999999418 back. The rest of the code I have works fine (in other words “Earned” is returning $112.00, which is correct in spite of the 15.9999…..

Here’s what I have:
Fields: [Time In], [Time Out], [Hrs Worked], [Call Pay], [# Cases], [Earned]. (That’s the tab order also.)
On Table: Time in & Out are Date/Time-General Date format. Hrs Worked is Text. Call pay is yes/no. Earned is Currency.
Format on form: Time in & Out are Medium time. Hrs worked is #.# with two decimals places (Don’t ask me why, it’s been so long since I created the db that I don’t remember). Call Pay (checkbox) and # Cases (textbox) have no format. Earned is currency.

Assumptions:
1. Employee earns $30.00/hr. unless “call pay” is checked true in which case employee earns $7.00/hr.
2. If employee is being paid “Case Pay”, the employee is paid $300.00/case regardless of hours worked or if call pay is true or false. So “Earned" is always [# Cases] * 300.

Here’s what I have for code: (Beware the following may appear dysfunctional! Experienced developers may want to turn away!)

Private Sub Time_Out_Exit(Cancel As Integer)
Hrs_Worked = ([Time_Out] - [Time_In]) * 24
End Sub

Private Sub Call_Pay_Exit(Cancel As Integer)
If Forms![frm_my_hours].Call_Pay = False Then
Earned = [Hrs_Worked] * 30
Else
Earned = [Hrs_Worked] * 7
End If
End Sub

Private Sub Number_of_Cases_Exit(Cancel As Integer)
If IsNull(Forms![frm_my_hours].Number_of_Cases) Then
Earned = [Earned]
Else
Earned = [Number_of_Cases] * 300
End If
End Sub

Any opinions on what's wrong and how I can fix it or make the logic better in general? One last thing; I'm thinking of changing [Call Pay] to an option group with [regular pay],[call pay] and [case pay] as options that drive the calculation logic. I'm thinking this may improve or even maybe simply things.

Grateful for any help.

[This message has been edited by Stephenn (edited 03-30-2002).]

 

[Stephenn]

Can anyone comment on why I might be getting "15.9999999999418"? I don't always get this type of return. I might enter different hours of the day (which still total to 16 hours) and get the normal 16 returned.
I'd appreciate any comments or suggestions.

 

[Pat Hartman]

The problem is with floating point arithmatic. You can force Access to use fixed point arithmatic by defining all your numeric fields as currency or you can write your own rounding functions to eliminate the floating point error. I don't have the exact document address but if you go to www.fmsinc.com and find their papers section, you'll see an excellent article titled, "When Access Math doesn't Add Up". This article explains the problem in some detail.

 

[Stephenn]

Thanks Pat, the article was very enlightening. The code suggested, however, is above my beginner level. Could you or anyone suggest something for the above data (Hrs_Worked = ([Time_Out] - [Time_In]) * 24)? Seems like I just need something to round the number up to two decimals.
Thanks again.