Mintues between start time and end-time.

[Ezeasy]

I have both a start time and finish time, in my table define as date & time. I would like to calculate the amount of time between the 2 fields in minutes; I used the “datediff” MIN: DateDiff("n",[Table1]![strTime],[Table1]![endTime]) and it works great when my time runs over the 24 hours I have problems not sure what to do. Ex strtime= 10:30:00 PM and endtime = 12:15:00 AM it returns –1335 should be 105 minutes. Any help would be appreciative.
Thanks in Adavance

 

[jitter]

My only thought is to throw the date on there too.

DateDiff("n","1/1/2002 10:30 PM","1/2/2002 12:15 AM&quot

That is a thought. I am not sure how else to let access know exactly.

 

[Ezeasy]

Sorry if this is easy but how would I add the date to the time field. I have the start date not the end date. I’m fairly new to access.

 

[allanon]

Let's assume that [Field1] is your original date/time:

IE: 1/1/2002 10:30 PM

Let's assume that [field2] is you second time

IE: 11:15 PM

I would do it like this:

Dim MyTime as string

MyTime = Format([Field1], "mm/dd/yyyy&quot & " " & [field2]

Then you calc should be:

datediff("n", cdate([field1]), cdate(MyTime))


anyhow. something like that.

 

[Ezeasy]

What I mean is I have the start date as a separate field not as one date and time. And where do i put this code?????

 

[allanon]

Ok. Here is what I would do. Create a function called ElapseMin in the modules like this:

Public Function ElapseMin(ByVal StartTime as date, ByVal EndTime as Date) as Double
Dim StartDate as string
Dim EndDate as string
Dim dStartDate as date
Dim dEndDate as date

StartDate = Format(Now, "mmm/dd/yyy&quot & " " & Format(StartTime, "hh:mm AMPM&quot
dStartDate = cdate(StartDate)
EndDate = Format(Now, "mmm/dd/yyy&quot & " " & Format(EndTime, "hh:mm AMPM&quot
dEndDate = cdate(EndDate)

If dStartDate > dEndDate then
dEndDate = dateadd("d", dEndDate)
end if

ElapseMin = DateDiff("n", dStartDate, dEndDate)
End Function

The in your update query (or where ever you need it) use this function like so:

MIN: ElapseMin(cdate([Table1]![strTime]),cdate([Table1]![endTime]))


That should pretty much do it. Please remember that this is not tried or test and there is no error checking in it. It will most likely need debuging BUT it should be close.

 

[Ezeasy]

Thanks for your help I'll give it a try.