How to convert Number to Time in Table

[sushmitha]

I have a table with Product Name, Product ID, Product Type. Product Type is P(Percentage), T(Time). We have a form to enter data into this table.

Few products are with type T. Time should be mm:ss format. We can enter the value only as number in Form. i.e if we enter 93 it should be converted to 1:33 sec. in table

How can we do this in Access ??

Let me know how can we convert number, text datatype to Time format

This is our priority request. Please help me

 

[MSAccessRookie]

I have a table with Product Name, Product ID, Product Type. Product Type is P(Percentage), T(Time). We have a form to enter data into this table.

Few products are with type T. Time should be mm:ss format. We can enter the value only as number in Form. i.e if we enter 93 it should be converted to 1:33 sec. in table

How can we do this in Access ??

Let me know how can we convert number, text datatype to Time format

This is our priority request. Please help me

If you are looking to convert a number to time, the Mod Function will be useful in determining the Hours from the Minutes.

 

[sushmitha]

If you are looking to convert a number to time, the Mod Function will be useful in determining the Hours from the Minutes.

I need to find mm:ss from a number..There are no hours..Time should be in mm:ss

 

[WayPay]

Also worth checking out: TimeSerial(), DateSerial(), DatePart() and Format(). The xSerial()-functions "overflow", so you can do this:

Format(TimeSerial(0, 0, 93), "Nn:Ss") ' yields 01:33

 

[Singh400]

Wouldn't

Format(<fieldname>, "mm:ss")

work?

 

[WayPay]

Wouldn't

Format(<fieldname>, "mm:ss")

work?

Yes, it would. It would do something different, though . The field is a number field containing the number of seconds. Format( 93, "mm:ss" ) will yield 93 days.

 

[raskew]

Hi -

Try:

? format(93/86400, "nn:ss")
01:33

...where 86400 represents the number of seconds in a day.

HTH -- Bob

 

[WayPay]

I think this problem was already solved twice 5 posts ago

 

[Singh400]

Yes, it would. It would do something different, though . The field is a number field containing the number of seconds. Format( 93, "mm:ss" ) will yield 93 days.

LMAO. Thats what staring at horribly written SQL statements will do to you. Send ya loopy!

 

[WayPay]

Please note that I do not endorse the use of hardcoded integers in production systems .

 

[sushmitha]

Hi -

Try:

? format(93/86400, "nn:ss")
01:33

...where 86400 represents the number of seconds in a day.

HTH -- Bob

It is showing in 12:01 etc in format
I have values entered into table like 0.29, 1.34 etc. I need to convert these values to mm:ss for few of the names where type is Time

I tried to use both the formula above which didnot work and showing in 12:00 format

 

[raskew]

Hi -

I'm lost.

I have values entered into table like 0.29, 1.34 etc.

What do 0.29 and 1.34 represent?

I need to convert these values to mm:ss for few of the names where type is Time

I'm really lost! Note that when formatting minutes: seconds, it's "nn:ss" rather than "mm:ss". "mm" represents months.

Are you trying to store more than one datatype in the same field?

I tried to use both the formula above which didnot work and showing in 12:00 format

In the immediate (debug) window type:
? format(93/86400, "nn:ss") <return>

Does it not return: 01:33 ?

Is this not what you're after?

Please clarify.

Bob

 

[sushmitha]

Hi -

I'm lost.



What do 0.29 and 1.34 represent?



I'm really lost! Note that when formatting minutes: seconds, it's "nn:ss" rather than "mm:ss". "mm" represents months.

Are you trying to store more than one datatype in the same field?



In the immediate (debug) window type:
? format(93/86400, "nn:ss") <return>

Does it not return: 01:33 ?

Is this not what you're after?

Please clarify.
quote]

I have a table with field name 'Value' datatype number.
I also have Name field with data type text.

Value is different for each name. Few values as % and few of them are Time
But in database we enter only number format Ex: 1.23, 0.45 etc

When I am transforming data in Reports I need to convert them to Time in nn:ss format for few names

For Ex: If name is EasternTime then value is in nn:ss like 2:34

 

[raskew]

What do 0.29 and 1.34 represent?

Please provide examples!

Back in post #1 you said:

i.e if we enter 93 it should be converted to 1:33 sec. in table

Does 0.29 represent 29 seconds and 1.34 represent 94 seconds?

Please clarify.

Bob

 

[The_Doc_Man]

I think the correct format (regardless of not wanting hours) is "hhh:nn:ss" because that third "h" says "elapsed-time format." Which will lose the "12:01" problem.

However, the bit about TimeSerial doing auto-overflow is constructive as well.

 

[sushmitha]

Please provide examples!

Back in post #1 you said:



Does 0.29 represent 29 seconds and 1.34 represent 94 seconds?

Please clarify.

Bob

Yes. 0.29 represents 29 sec and 1.34 represents 94 seconds

 

[Thales750]

moving to top