Format Time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings !

I have a field named [TimeVariance] in HH:NN:SS format.
I am using a query in query design view such as TimeVariance1:
Format([TimeVariance], "hh") & Format([TimeVariance], "nn")
to get only hour and minutes part.

It worked fine unless [TimeVariance] is less than 24 hours.
If [TimeVariance] is 24:03:21
[TimeVariance1] gets 24:03:2124:03:21 in my query.

I want to my query work even though it is larger than 24 hours.
Or better ideas?
 
Hi,


Int (24 * timeVariance) & Format( timeVariance, ":nn")

I assumed timeVariance is always positive.



Hoping it may help,
Vanderghast, Access MVP
 
Jay said:
Greetings !

I have a field named [TimeVariance] in HH:NN:SS format.
I am using a query in query design view such as TimeVariance1:
Format([TimeVariance], "hh") & Format([TimeVariance], "nn")
to get only hour and minutes part.

It worked fine unless [TimeVariance] is less than 24 hours.
If [TimeVariance] is 24:03:21
[TimeVariance1] gets 24:03:2124:03:21 in my query.
Click to expand...

Date/time values are best used for exact points in time, not durations: a
Date/Time value will roll over into a new day (actually December 31, 1899,
since midnight, December 30, 1899 is the zero point) if it exceeds 24 hours.
I would suggest that you NOT store time variance in a Date/Time field.
Instead, calculate the time difference in minutes using an expression like

TimeVariance: DateDiff("n", [ExpectedTime], [ActualTime])

This can be used in an expression to calculate the hh:nn format:

[TimeVariance] \ 60 & ":" & Format([TimeVariance] MOD 60, "00")

John W. Vinson/MVP
 
Back
Top