I have a field [field1] that has data as you see below
221134123
211223324
183412
1234
I added this expression Format([field1],"hh:nn:ss") and get the #num! error. I know it has something to do with the format but not sure what function to use.
I have a field [field1] that has data as you see below
221134123
211223324
183412
1234
I added this expression Format([field1],"hh:nn:ss") and get the #num! error. I know it has something to do with the format but not sure what function to use.
the expression is trying to convert the numbers to time format. You'll get #Num! where the number just doesn't make sense to be converted to time. The whole number (to the left of the decimal) represent the date, the decimal number represents a time. Your trying to show the time for these numbers but they don't have the decimal (or time) part.
For your reference: https://docs.microsoft.com/en-us/off...-datetime-data
The first two values have too many digits for the function to handle. Can truncate to drop the last 3.
Format(Left(field1, 6), "hh:nn:ss")
This eliminates the #Num error. With or without this change, I get only zeros in the output. So convert to a string with decimal simply by concatenating period.
Format("." & field1, "hh:nn:ss")
Now I get some time values but no idea if they are what you expect.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Hi June7, the second option does return values but you're right, not what I expect.
My table has:
220201675
10533937
The results I get are:
05:17:05
02:34:34
I should be getting:
10:20:16 or 22:20:16
10:56:39
I probably need to * by the number of seconds in a day or something to that effect... great place to start.
Thanks June7
kd, this is a very interesting read you provided, thanks.
Not understanding your expected output.
220201675 --> 22:20:16
10533937 --> 10:56:39
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Its fat finger problem June1 - the 1st one is right. It could be returned in a 24 hour increment or 12 hour am/pm but without the am/pm.
10533937 --> 10:53:39
Gicu, I can't thank you enough for taking the time and even creating a db. Thanks for your example and work, it worked perfectly.
Is there not an extra 0 in that first value - between 22 and 20?
Glad you have solution.
For those who don't want to download file, the query is:
SELECT Table1.TimeField, Left(CStr([TimeField]) & "000000",6) AS HHMMSS, Format([HHMMSS],"00\:00\:00") AS FormattedHHMMSS FROM Table1;
I tested the expression with and without the \ character and as text and number field - all worked.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
my apologies - I assumed everything was working properly.
Okay what was provided worked but I did not account for the raw file (it's not great).
So Table1 sometimes has values such as 221134123 which is converted to 22:11:34 in query1. The issue is that I also get data such as 920000 which should be returned as 9:20:00 but I get 92:00:00. I'm not sure why some of the values returned get formatted correctly while others don't.
How should code "know" that 920000 should be 9:20:00?
When you look at the data, what rule do you apply to determine how to section the value?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
I THINK I will format that column to add 0s if the length of that field is less than 9... need to give it some more thought.
Just tagging on zeros to end of value won't return 9:20:00. Would need a leading zero: 0920000 should return 09:20:00
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Perhaps use Format() and the required string to get a base to start with, THEN split the data?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba