How does one handle rollovers when doing subtraction with time?

[llyal]

How does one handle rollovers when doing subtraction with time?

I find that when adding time, VBA handles rollovers at the 24-hour mark gracefully; but when i subtract hours, VBA gives me weird results;

Here is some code:

' subtract 12 hours, if resuting time is larger than current time then a 24-hour rollover occurred
' show current time and calculated time
If (TimeValue(TimeSerial(Hour(Time) - 12, Minute(Time), Second(Time)))) > TimeValue(Time()) Then
MsgBox "rollover"
MsgBox TimeValue(Time()) & " " & TimeValue(TimeSerial(Hour(Time) - 23, Minute(Time), Second(Time)))
End If

If the current time is 10:20:00 AM, I might get something like 12:28:04 PM (which is totally wrong);

how should i do time subtraction properly to handle the 24-hour rollover?

Thank you!

--llyal

 

[simongallop]

For the displayed problem of subtracting 12 hours, change to date value - 0.5
(half a day=12 hours)

Having not looked into your prob, can't comment for any other time subtractions

 

[llyal]

Thanks for the Help! Since all I wanted to do was subtract even hours, like 2, 6, 12, etc, your solution works great!

I would still like, for future projects, to learn how to subtract exact time; example, subtract 2 hours 5 secs from 1:30 AM- how would i do this successfully (handle the 24-hour rollover, etc)?

Thank you!

--llyal

 

[Jack Cowley]

This article should answer all your questions about how to work with Access Dates and Time...
http://support.microsoft.com/support/kb/articles/Q88/6/57.ASP?LN=EN-US&SD=gn&FR=0&qry=q88657&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=ACC97

 

[Crispy]

I had a look in the Microsoft knowledge base and the problem is with the way Access stores date/time values. However it does post some code which I have adapted and seems to work.

Create a new module in your db and paste the following into it:

Function GetTimeCardTotal()
Dim db As Database, rs As Recordset
Dim totalhours As Long, totalminutes As Long
Dim days As Long, hours As Long, minutes As Long
Dim interval As Variant, j As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("table1")
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![f1]
rs.MoveNext
Wend
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

GetTimeCardTotal = totalhours & ":" & minutes

End Function

In the above code, "Table1" represents the table orr query on which your report is based, and "F1" represents your date/time field.

Once you have done this, set the recordsource of your textbox to:

=GetTimeCardTotal()

Should work, hopefully.....

 

[Crispy]

Ignore the above! It was a reply to a different post!!!

 

[boblarson]

Use the DateDiff function to get at what you want.