Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Date + Time _ Number Data Type To Time Format

Posted by: pglibra Dec 7 2017, 10:34 AM

I need help converting a number data type field to time format.
Our administrator has the field type locked down so I cannot change it from Number to Date/Time.
Currently, the Field is simply "UPDATE_TIME" and it returns, for example, 103826, which would translate into 10:38am and 26 seconds.
What expression format do I use to display outcome in time format (minus the seconds) and if possilble to show AM/PM? So simply 10:38am.

Thank you

Posted by: theDBguy Dec 7 2017, 10:41 AM


Welcome to UtterAccess!

For an expression, you could try something like:

Left(CStr([UPDATE_TIME]),2) & ":" & Mid(CStr([UPDATE_TIME),3,2) & IIf([UPDATE_TIME]<120000,"am","pm")

Hope it helps...

Posted by: doctor9 Dec 7 2017, 10:43 AM


With the given data, you could easily convert it using the TimeSerial() function and a bit of numerical parsing. Do you also need a date aspect?

10 Hours = Int((103826 Mod 1000000) / 10000)
38 Minutes = Int((103826 Mod 10000) / 100)
26 Seconds = 103826 Mod 100

Therefore, you could do this:

Public Function RealUpdateTime(lngInput As Long) As Date

'   103826 is 10:38:26am
    RealUpdateTime = TimeSerial(Int((lngInput Mod 1000000) / 10000), Int((lngInput Mod 10000) / 100), lngInput Mod 100)

End Function

Hope this helps,


EDIT: theDBGuy.... I think that might fail for times that don't have a two-digit number of hours, like 91506. But then again, it would be good to know how OP stores 12:15:00am and 12:15:00pm. Are you using a sort of military time format?

Posted by: pglibra Dec 7 2017, 10:49 AM

the DBGuy,
Thanks for the quick response. (Glad I joined this group!!!)
However, I copied and pasted your expression (I know you said untested) and it returned "The expression you entered has a function containing the wrong number of arguments."

Posted by: pglibra Dec 7 2017, 10:53 AM

Thanks for your quick response.
The field is not a user input.
It comes from a query. The field inside the table is UPDATE_TIME and the Data Type for the field is Number.
So looking for how the query field format should be written.
Hope this helps explain.

Thank you

Posted by: theDBguy Dec 7 2017, 11:10 AM

Hi PG,

Regarding the error for wrong number of arguments, can you please post the exact expression you used? Thanks.

Posted by: doctor9 Dec 7 2017, 11:11 AM


My suggestion was a simple User Defined Function. "lngInput" is just the variable name I used in my suggested code. It has nothing to do with the name of the field in your table. Whatever long integer number is passed to the function is referred to as lngInput within the function. In a query, you'd call the function with something like this:

ActualUpdate: RealUpdateTime([NameOfTheFieldStoringTheNumberValue])

I'm using that long name because you didn't tell us the name of the field from your table that's holding the numeric value.

If you want to use an expression within your query instead of calling a user defined fuction, you could do this in the Grid Design View:

ActualUpdate: TimeSerial(Int(([NameOfTheFieldStoringTheNumberValue] Mod 1000000) / 10000), Int(([NameOfTheFieldStoringTheNumberValue] Mod 10000) / 100), [NameOfTheFieldStoringTheNumberValue] Mod 100)

Hope this helps,


EDIT: It might also be helpful to know what 12:15am and 12:15pm look like using your numeric format, if that becomes an issue.

Posted by: pglibra Dec 7 2017, 11:14 AM

Here you go.

Left(CStr([UPDATE_TIME]),2) & ":" & Mid(CStr([UPDATE_TIME),3,2) & IIf([UPDATE_TIME]<120000,"am","pm")

I'm missing something, right?

Posted by: theDBguy Dec 7 2017, 11:18 AM


Thanks. Yeah, I missed a closing "]". Try:

Left(CStr([UPDATE_TIME]),2) & ":" & Mid(CStr([UPDATE_TIME]),3,2) & IIf([UPDATE_TIME]<120000,"am","pm")

Hope it helps...

Posted by: pglibra Dec 7 2017, 11:25 AM

Getting closer...
1) Times after 12:00 are showing up in military format, i.e. 16:13pm so would like it to read as 4:13pm (for those who cannot convert..haha)
2) I had one time that previously came out as 95532 that now comes out as 95:53am so issue would be with times before 10am.


Posted by: pglibra Dec 7 2017, 11:29 AM

Yours worked perfect!!
But still shows the seconds.

Posted by: theDBguy Dec 7 2017, 11:34 AM

Hi PG,

I guess it's better to just go with the Doctor's solution then.


Posted by: pglibra Dec 7 2017, 12:31 PM

Thank you both for your suggestions/solutions!!!
Mark this one as solved...haha.

ps..I'm glad I joined this group and now know where to go when I get stuck.

Have a great day.


Posted by: theDBguy Dec 7 2017, 12:36 PM


Doctor9 and I were happy to assist. Good luck with your project.

Posted by: doctor9 Dec 7 2017, 01:45 PM


Now that you've got the value in date/time format, you can format the textbox to display it any way you like:

12:15 PM
12:15 p

Or, if you want to ignore the seconds portion entirely, just change the expression to have a hard-coded zero instead of the "lngInput/[fieldname] Mod 100" bit.


Posted by: pglibra Dec 7 2017, 02:14 PM

I formatted the text box to Medium Time.

Thanks again.