Still problems with time

  • Thread starter Thread starter Box 666
  • Start date Start date
B

Box 666

I have been trying to work out the difference between "start time" and "end
time" in hours and mins. I was directed to the following module which does
exactly what I want ..... however I cannot then query it further i.e. I
cannot find an expression to find all values greater than 16hours.(it tells
me the results are formatted as a hours:minutes string)
Could somebody please advise of the correct format to query the results from
the following.

With thanks - Bob

Public Function HoursAndMinutes(interval As Variant) As String
'***********************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'***********************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))

totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60

totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60

If seconds > 30 Then minutes = minutes + 1 ' round up the minutes and
If minutes > 59 Then hours = hours + 1: minutes = 0 ' adjust hours

HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function
 
Bob,

You can't work with it any further because the function converts the
interval to a text string!
Date/Time fields in Access are basically numeric fields (Double, I think) in
which the integer part is the date (actually days since 01/01/1900), and the
decimal part is the time (1 = 24 hrs, so 0.5 = 12 hrs i.e. 12:00, 0.75 = 18
hrs i.e. 18:00 etc.).
In my experience the safest way to work with date and time differences is to
store the data in its default type (sso they are actually stored as
numbers), work with it as numbers (adding, subtracting etc) and apply a
format only on the results for display purposes.

HTH,
Nikos
 
Back
Top