Time Difference When Past Midnight

[StevenCV]

Hello,



I have a field that works out the difference in time between two fields. However, whenever the end time goes past midnight, it calculates the difference as a minus figure. I understand that this is because of the date issue, but I cannot seem to find a way around it.

Here is what I have so far:

time_of_referral field. This is the start time. It is a short time field that is filled in by the end user.
time_of_arrival field. This is the end time. It is a short time field that is filled in by the end user.
Text31 field. This is not visible to the end user, and has the following control source =DateDiff("n",[time_of_referral],[time_of_arrival])
Text33 field. This is an unbound text box with the following control source =[text31]\60 & Format([text31] Mod 60,"\:00")

This works out the time difference and presents it as hours and minutes.


I have seen solutions that add a day to the end time, but unfortunately this does not work when the end time DOESN'T go past midnight. Some will go past midnight, some won't.

Is there a way of working this out, because this is very frustrating.

Thanks in advance.

[June7]

Does this help http://access.mvps.org/access/datetime/date0004.htm

[StevenCV]

That helps massively. A perfect solution - thank you very much!

[StevenCV]

I have another issue now, following on from this.

I have another field called text35 with this control source:
=IIf([Text31]>120,"No","Yes")

Text31 is the hidden text box that calculated the datediff for my time - the field which is now redundant thanks to your solution above.

How can I get text35 to say Yes or No if the time difference is over 2 hours? Can I do it by calculating on Text33, which now contains your solution?

[June7]

That expression returns elapsed time as hours:minutes. Need decimal hours. Instead of 11:15, need 11.25.

=IIf(Val(Text33) + Right(Text33,2)/60 > 2, "No", "Yes")

This certainly would have been simpler if the date part was saved with the time input.

[StevenCV]

June, I think you are my favourite person in the whole world. Thank you for that, it works brilliantly.