Results 1 to 14 of 14
  1. #1
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100

    How to fix #num! error?

    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.

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    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

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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.

  4. #4
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    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

  5. #5
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    kd, this is a very interesting read you provided, thanks.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please have a look at the sample I created for you. It looks like you store the time component as a text (string) not the decimal part of a true Date\Time field. So what you need is a bit different than what was suggested.
    Click image for larger version. 

Name:	Screenshot 2021-11-05 135702.png 
Views:	25 
Size:	34.1 KB 
ID:	46553
    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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.

  8. #8
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    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.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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.

  10. #10
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    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.
    Attached Files Attached Files

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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.

  12. #12
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    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.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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.

  14. #14
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 07-15-2019, 10:20 AM
  2. Replies: 1
    Last Post: 07-26-2016, 06:34 AM
  3. Replies: 3
    Last Post: 01-23-2014, 07:49 AM
  4. Replies: 0
    Last Post: 07-16-2012, 05:42 AM
  5. Replies: 6
    Last Post: 05-30-2012, 12:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums