How do I add time greater than 24 hours in Access 2000?

L

LN5

For example, a total of 44:33:27 results in 8:33:27. I need to use the final
time (44:33:27) to divide by another time to find the percentage and am
receiving the incorrect answer due to the inability to obtain the correct
result for the addition of time greater than 24 hours.
 
D

Douglas J. Steele

Realistically, Access isn't designed to do what you're trying to do. The
only time-related data type is intended to be used as a timestamp (a
specific date/time), rather than a duration.

The normal advice is to store your durations as numbers, not dates (for
example, total seconds in a Long Integer), and write your own functions to
convert between the two.

Alternatively, check my October, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/djsteele/SmartAccess.html
 
A

Arvin Meyer [MVP]

Dates calculated using the DateDiff or DateAdd function will return the
correct number of hours, minutes, and seconds which can then be formated
using the MOD operator to display any way you want. For instance:

Dim dtNow As Date
Dim lngTmp As Long
Dim lngTmp2 As Long
Dim lngTmp3 As Long
Dim lngTmp4 As Long
Const conDate As Date

conDate = Me.txtDateEnd

dtNow = Now
lngTmp = DateDiff("s", dtNow, conDate)
lngTmp2 = CLng(lngTmp / 86400)
lngTmp3 = lngTmp2 Mod 24
lngTmp4 = (lngTmp3) Mod 60

Me.txtResult = CStr(Int(conDate - dtNow)) & IIf(conDate - dtNow > 1, " days
", " day ") & Format$(conDate - dtNow, "hh:nn:ss")
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Top