Change numbers to time format

[Pietro]

Hi,

In my query I've a "handlingtime" field contacins numbers that indicate
minutes.
I want to create a new field to have these numbers changed into time format.
Example :
handlingtime NewField
72.2 01:12:12
59 00:59:00
120 2:00:00

 

[Krzysztof Pozorek [MVP]]

(...)

In my query I've a "handlingtime" field contacins numbers that
indicate
minutes.
I want to create a new field to have these numbers changed into time
format.
Example :
handlingtime NewField
72.2 01:12:12
59 00:59:00
120 2:00:00

Use DateAdd function:
DateAdd("n",handlingtime,0)

Examples (in immediate window):
?DateAdd("n",72.2,0)
01:12:00

?DateAdd("n",59,0)
00:59:00

?DateAdd("n",120,0)
02:00:00

K.P.

 

[Pietro]

Thank you Krzysztof for your reply.
I did not understand your answer, I need to do the following an a query not
VB.
Thanx again

 

[Krzysztof Pozorek [MVP]]

(...)

I did not understand your answer, I need to do the following an a query
not
VB.

In that event, write something like this:

SELECT handlingtime, DateAdd("n",handlingtime,0) AS NewField
FROM Table1;


K.P.

 

[Pietro]

Thank you Krzysztof
It works fine for the minutes and hours, but still it does not work with
seconds.
For example it changes 1.33 minutes to 0:01:00 and 70.66 minutes to 1:10:00
so the seconds are always 00

What do you recommand ?

 

[John Spencer]

That won't work as written since the dateAdd function use an integer value so
the decimal portion gets truncated - 72.2 will return 01:12:00 and not 01:12:12.

As long as the HandlingTime is under 24 hours (less than 1440), you should be
able to use this expression in your query:
Format(DateAdd("s",HandlingTime * 60, 0),"hh:nn:ss")

Post back for a more complex solution if the handling time is over 1440 minutes.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Pietro]

Thank you John veru much...
The below works fine for hours, minutes and seconds.
What about if i'd like to add days?

 

[Douglas J. Steele]

You'd have to write a function to do the formatting for you. That's because,
to Access, a Date/Time value is an eight-byte floating pointing number where
the integer portion represents the date as the number of days relative to
30 Dec, 1899, and the decimal portion represents the time as a fraction of a
day. If you've strictly got a time, to Access it's that time on 30 Dec,
1899. If you have a time that exceeds 24 hours, that means that it's going
to be a time on a different day to Access. For example, 0.5 is Noon (half of
a day), 1.5 would be Noon on 31 Dec, 1899, and 2.5 would be Noon on 1 Jan,
1900.

I talked about this in my October, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[John Spencer]

The following will probably work to give you the days, hours, minutes and
seconds in the format d:hh:mm:ss. If you want some other delimiter between
the days and the rest change the ":" to " Days "


HandlingTime\1440 & ":" & Format(DateAdd("s",HandlingTime * 60, 0),"hh:nn:ss")

That is a reverse slash so you will be doing integer division on the value of
HandlingTime.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County