Mean MrMustard
Diamond Member
(Also posted in Software Forum)
I am building a report in Access. The database records all phone instances i.e. how long a call lasted, how long you were idle, how long a call was on hold, etc... Anyway the database records these numbers in seconds.
For the report I must convert seconds into hours, minutes, and seconds "hh:mm:ss." Can I do this in SQL or do I have to use VBA? I know how to figure it out, that's easy; I just don't know how to code it correctly in VBA. I have a sample of what I'm trying to do.
I guess you could call this pseudocode; its logically correct but not VB code. For instance you had 31081 seconds...
Dim x, y, z, a, b, s, h, m
'gets data from query and finds hours 31081/60 = 518.016/60= 8.63361
x = ([Specialist Master Query].[ACD Time] / 60) / 60
' gets only the whole numbers (roundsdown no matter what) 8
y = rounddown( x )
' 08
h = "0" & y
' (8.63361 - 8) * 60 = 38.016
z = (x - y) * 60
' gets whole number again 38
a = rounddown( z )
' this just makes sure that the minutes will take up 2 digits "00:00:00" 38
If a <= 9 Then
m = "0" & a
Else
m = a
End If
' Int((38.016 - 38) * 60 = 1
b = Int((z - a) * 60)
'same as minutes 01
If b <= 9 Then
s = "0" & b
Else
s = b
End If
'[ACD Time] would be name of the text box that contains the time 08:38:01
[ACD Time] = h & ":" & m & ":" & s
Please post any questions or suggestions. I tried to implement this in a module but to no avail. Oh yeah, this isn't for school as you won't be doing my homework for me. I spent the better part of 5 hours fiddling around with this at work. I don't have internet access at work so I wasn't able to post earlier or google it.
I am building a report in Access. The database records all phone instances i.e. how long a call lasted, how long you were idle, how long a call was on hold, etc... Anyway the database records these numbers in seconds.
For the report I must convert seconds into hours, minutes, and seconds "hh:mm:ss." Can I do this in SQL or do I have to use VBA? I know how to figure it out, that's easy; I just don't know how to code it correctly in VBA. I have a sample of what I'm trying to do.
I guess you could call this pseudocode; its logically correct but not VB code. For instance you had 31081 seconds...
Dim x, y, z, a, b, s, h, m
'gets data from query and finds hours 31081/60 = 518.016/60= 8.63361
x = ([Specialist Master Query].[ACD Time] / 60) / 60
' gets only the whole numbers (roundsdown no matter what) 8
y = rounddown( x )
' 08
h = "0" & y
' (8.63361 - 8) * 60 = 38.016
z = (x - y) * 60
' gets whole number again 38
a = rounddown( z )
' this just makes sure that the minutes will take up 2 digits "00:00:00" 38
If a <= 9 Then
m = "0" & a
Else
m = a
End If
' Int((38.016 - 38) * 60 = 1
b = Int((z - a) * 60)
'same as minutes 01
If b <= 9 Then
s = "0" & b
Else
s = b
End If
'[ACD Time] would be name of the text box that contains the time 08:38:01
[ACD Time] = h & ":" & m & ":" & s
Please post any questions or suggestions. I tried to implement this in a module but to no avail. Oh yeah, this isn't for school as you won't be doing my homework for me. I spent the better part of 5 hours fiddling around with this at work. I don't have internet access at work so I wasn't able to post earlier or google it.
