Calculating time. 1

[ddmtn546]

Hello All! I need to calculate time difference in minutes on a form where the user enters: Date, Start Time, End Time. These times use military time.

In a query, I use:
1TimeDiff: DateDiff("s",[1StartTime],[1EndTime])/60

This works fine except when crossing midnight where going from, say, 2300 to 0000 or 0100 hrs the next day. I get negative values returned.

How would the code look to accomplish this?

Thanks in advance. ddmtn546.

 

[lupins46]

iif(1starttime<=1endtime,DateDiff("s",[1StartTime],[1EndTime])/60, (DateDiff("s",[1StartTime],[1EndTime])+86400)/60)

 

[KenReay]

Hi

You DateDiff() code is just fine, but if the start and finish time cross the midnight boundary, you need to include a start and finish 'time' which includes the date, if you do that Access will do the rest

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ddmtn546]

Lupins 46,
Thanks for the response, however I get a syntax error at the "1starttime" following the iif command, any ideas?

iif(1starttime<=1endtime,DateDiff("s",[1StartTime],[1EndTime])/60, (DateDiff("s",[1StartTime],[1EndTime])+86400)/60)

 

[lupins46]

Put [ ] around 1starttime and 1endtime.

 

[ddmtn546]

ddmtn546

Hey Lupins46, I', not sure how this works, but it does. I'll use it. Maybe you could email an explanation. Thanks,
ddmtn546@bellsouth.net

 

[KenReay]

Hi

It is simply adding a day (in Seconds) to the answer, so it works for one span of 12 midnight, but not 2 or 3 etc

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK