Time Question

[dc303]

Hi can any one help me, i have two fields in my query one is StartTime and the other EndTime both ar formated as short time, i want to compare the start time and end time and return the value in minutes however i need this to include midnight but i cant get it to work.

i keep getting -values for times that go beyond midnight can any one help?

thanks

Davie

 

[Brianwarnock]

To do what you want you also need a startdate and enddate.
A couple of years ago I was asked to do something similar for delays which could go over several days and I wrote the following function which may help you. I was new to ACCESS at the time but it works

Brian

Function delay(indate, outdate, intime, outime)

Dim t1 As Long
Dim t2 As Integer
Dim t3 As Long
Dim days As Integer
Dim time As Date
Dim t4 As Integer


t1 = DateDiff("n", indate, outdate)
t2 = DateDiff("n", intime, outime)
t3 = t1 + t2
days = Int(t3 / 1440)
t4 = t3 Mod 1440
time = TimeSerial(0, t4, 0)
delay = [days] & " /  " & [time]



End Function

 

[ScottGem]

Date/Time fields can store both date and time. The date is stored as a serial number counting the days from 12/30/1899. The time is stored as a fraction of a day where .25 = 6 hrs.

Therefore, if you only store the time (decimal) then you can't do accurate time span calcs. If you store the date as well as the time, you can still display the time only using a field format, but do your calcs properly. The expressions:

=DateDiff("n",[Start],[End])

will retunr the time difference in minutes.

 

[dc303]

Date/Time fields can store both date and time. The date is stored as a serial number counting the days from 12/30/1899. The time is stored as a fraction of a day where .25 = 6 hrs.

Therefore, if you only store the time (decimal) then you can't do accurate time span calcs. If you store the date as well as the time, you can still display the time only using a field format, but do your calcs properly. The expressions:

=DateDiff("n",[Start],[End])

will retunr the time difference in minutes.

Thanks this works fine but when the time values go over midnight i get a minus sign this is were i am realy stuck, the expression you quoted is fine but can you help me with the midnight part, many thanks Davie

 

[Jon K]

DateDiff("n",[StartTime], IIf([EndTime]<[StartTime], [EndTime]+1, [EndTime]))

That is, add one day to [EndTime] if [EndTime]<[StartTime]
.

 

[dc303]

DateDiff("n",[StartTime], IIf([EndTime]<[StartTime], [EndTime]+1, [EndTime]))

That is, add one day to [EndTime] if [EndTime]<[StartTime]
.

thanks jon this is exactly what i needed i have been struggling with this one since November cheers

Davie