Help in this SQL Statement

[joaompc]

Hello,

I have a table with a column where is displayed the time in seconds. I Add another column where I want to displayed the seconds in hours. I have the formula to do that. But it's not working. Can someone please correct this,

DoCmd.RunSQL "UPDATE TableA SET TableA.TimeHours = '" & (Format(Int(TableA.TimeSeconds / 3600), "##0") & ":" & Format(Int((TableA.TimeSeconds - (Int(TableA.TimeSeconds / 3600) * 3600)) / 60), "00") & ":" & Format((TableA.TimeSeconds Mod 60), "00")) & "';"

This will convert 234325 seconds to hh:mm:ss.

Thank you

 

[mdlueck]

Just quickly glancing at the SQL...

You do not appear to pad the hh:mm:ss time value between # characters. I believe Access will expect format #hh:mm:ss#.

 

[PeterF]

Why store a calculated value? To display the time in the required format simply use a unbound field on your form/report with as source:

dateadd("s", [YourControlnameHere] ,0)

 

[boblarson]

Why store a calculated value? To display the time in the required format simply use a unbound field on your form/report with as source:

dateadd("s", [YourControlnameHere] ,0)

Agreed, this calculation should not be stored. One can use a query in 99% of the places a table can be used.

 

[joaompc]

Agreed, this calculation should not be stored. One can use a query in 99% of the places a table can be used.

But I have a table with at least 100 records. I need to add a column that display the seconds on a specific hour format. That's why I was doing the SQL Statement.

Can I use your solution to populate a table?

 

[mdlueck]

joaompc, what the others are trying to point out to you is to sort the value only once, and dynamically compute the desired display format when you need it.

OTOH, did you see my reply and consider it?

 

[joaompc]

joaompc, what the others are trying to point out to you is to sort the value only once, and dynamically compute the desired display format when you need it.

OTOH, did you see my reply and consider it?

Thank you for your reply.

Where do you think I should add the # in the SQL Statement?

I understand the other solution, but with that I can only show the hours in a report or in a single text box!

 

[mdlueck]

TWhere do you think I should add the # in the SQL Statement?

Right around the entire time value, as I tried to illistrate.

Google for examples of how to set date and time values in Access queries.

I understand the other solution, but with that I can only show the hours in a report or in a single text box!

No, if you write a function in a VBA module, you can call that to translate from encoded format to any desired target (decoded) format. You can then instantly set any control - including Multiple Items additional unbound fields - to be the result of that conversion function.