UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Number Data Type To Time Format, Access 2013    
 
   
pglibra
post Dec 7 2017, 10:34 AM
Post#1



Posts: 8
Joined: 7-December 17



Hello,
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
PG
Go to the top of the page
 
theDBguy
post Dec 7 2017, 10:41 AM
Post#2


Access Wiki and Forums Moderator
Posts: 71,233
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

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")

(untested)
Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
doctor9
post Dec 7 2017, 10:43 AM
Post#3


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


PG,

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:
CODE
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,

Dennis

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?

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
pglibra
post Dec 7 2017, 10:49 AM
Post#4



Posts: 8
Joined: 7-December 17



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."
Go to the top of the page
 
pglibra
post Dec 7 2017, 10:53 AM
Post#5



Posts: 8
Joined: 7-December 17



Doctor9,
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
PG
Go to the top of the page
 
theDBguy
post Dec 7 2017, 11:10 AM
Post#6


Access Wiki and Forums Moderator
Posts: 71,233
Joined: 19-June 07
From: SunnySandyEggo


Hi PG,

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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
doctor9
post Dec 7 2017, 11:11 AM
Post#7


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


PG,

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,

Dennis

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.

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
pglibra
post Dec 7 2017, 11:14 AM
Post#8



Posts: 8
Joined: 7-December 17



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?
Go to the top of the page
 
theDBguy
post Dec 7 2017, 11:18 AM
Post#9


Access Wiki and Forums Moderator
Posts: 71,233
Joined: 19-June 07
From: SunnySandyEggo


Hi,

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...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
pglibra
post Dec 7 2017, 11:25 AM
Post#10



Posts: 8
Joined: 7-December 17



DBguy,
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.

pg
Go to the top of the page
 
pglibra
post Dec 7 2017, 11:29 AM
Post#11



Posts: 8
Joined: 7-December 17



Doctor9,
Yours worked perfect!!
But still shows the seconds.
Go to the top of the page
 
theDBguy
post Dec 7 2017, 11:34 AM
Post#12


Access Wiki and Forums Moderator
Posts: 71,233
Joined: 19-June 07
From: SunnySandyEggo


Hi PG,

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

Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
pglibra
post Dec 7 2017, 12:31 PM
Post#13



Posts: 8
Joined: 7-December 17



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.

pg
Go to the top of the page
 
theDBguy
post Dec 7 2017, 12:36 PM
Post#14


Access Wiki and Forums Moderator
Posts: 71,233
Joined: 19-June 07
From: SunnySandyEggo


Hi,

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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
doctor9
post Dec 7 2017, 01:45 PM
Post#15


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


PG,

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

12:15pm
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.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
pglibra
post Dec 7 2017, 02:14 PM
Post#16



Posts: 8
Joined: 7-December 17



I formatted the text box to Medium Time.

Thanks again.

pg
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 01:13 AM