Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283

    Formatting a number field to h:mm:ss

    Click image for larger version. 

Name:	Capture.PNG 
Views:	20 
Size:	3.1 KB 
ID:	41494

    I have a query that creates a table and every day adds the previous days data to it. In the attached you see total dials and talktime columns and then April 1 Dials and TT. Each day this table will get created with the next days date.

    The issue I am having is the TT column is a decimal number as seen in the attached but I need it to be displayed as h:mm:ss.



    Is there a way to update the format of every TT field to h:mm:ss as it gets added each day?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Fairly common topic. Review https://www.accessforums.net/showthread.php?t=21042

    The result will be a string value - not number and not date/time.
    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.

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    What unit of time is the number field?

    Your example show 0.09583, is that in hours, minutes, or seconds? I'd assume hours but you know what they say about that.

  4. #4
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by kd2017 View Post
    What unit of time is the number field?

    Your example show 0.09583, is that in hours, minutes, or seconds? I'd assume hours but you know what they say about that.

    It is the result of dividing seconds by 86000. The original data is 525 seconds when divided by 86000 results in 0.0061046511627907 and when formatted to h:mm:ss results in 0:08:47 (0 hours:8 minutes:47 seconds)

    The issue I have is the query is constantly growing each day as the previous days data gets added, but the form does not show these fields without me going in each day and adding them manually, which is not something I want to do. And if I try to pull right from the query, which does have the new data every day, the format is not right.

    Thanks for the help

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Query is a CROSSTAB? That is nature of CROSSTAB, dynamic fields. Limit of 255 fields. How much do you expect to 'grow'?
    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.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The issue I have is the query is constantly growing each day as the previous days data gets added, but the form does not show these fields without me going in each day
    just a suggestion. Have a subform on your form and in the sourceobject put

    query.nameofcrosstab

    with regards formatting, not tested but in the query design, show the properties for the value column and set the format property to hh:mm:ss or possibly shorttime

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How will format property setting correctly display elapsed time value?

    Isn't 525 seconds = 8 minutes and 45 seconds?
    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
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure what is not clear - the value is a decimal which as the OP has correctly identified is the number of seconds divided by the number of seconds in a day. So providing the value does not equal 1 or greater it can be formatted as a time - same as the format function but retaining its numerical status

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Formatting returns 00:08:47 but the elapsed time is 00:08:45 = 525 seconds. Formatting the quotient introduces an error.
    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
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    For clarity's sake there are 86,400 seconds in a day. Assuming that's what you mean then would this work?

    Code:
    Format(Int(YOUR_NUMBER * 24), "00:") & Format(Int(YOUR_NUMBER* 1440) - hrs * 60, "00:") & Format(Int(YOUR_NUMBER * 86400) - mins * 60 - hrs * 3600, "00")
    Also, I agree with June7 in that storing the seconds / 86400 (or 86000) introduces floating point rounding errors.

    A long can hold 2147483647 seconds ~= 596,523 hours, would that work for you?

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I had assumed the OP had made a typo - there are 86400 seconds in a day

    ?525/86400
    6.07638888888889E-03
    ?format(6.07638888888889E-03 ,"hh:mm:ss")
    00:08:45

    introduces floating point rounding errors.
    in terms of accuracy to 1 second, I don't agree
    ?1/86400
    1.15740740740741E-05

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    As often as I see this question, think I'd have it figured out by now.

    Makes more sense now.

    Think issue arises if elapsed time exceeds a day.

    That and my mapping background has me naturally gravitate to the calc instead of formatting. Calc would be needed to convert decimal to degrees/minutes/seconds.
    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.

  13. #13
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Ajax View Post
    ?format(6.07638888888889E-03 ,"hh:mm:ss")
    How about that! Didn't realize format did that.

    Per OP I assumed 525 = 00:08:47 rather than 00:08:45 so I thought I saw a 2 second error.

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Think issue arises if elapsed time exceeds a day.
    it does - of limited use but you can change the calculation slightly and include a day in the format which will get you up to 31 days

    ?format(25.15740740740741-(25.15740740740741>=1),"d hh:mm:ss")
    25 03:46:40

    but that only works if elapsed time is greater than a day - days will show as 30 if elapsed time is <1 which is clearly wrong.

    How about that! Didn't realize format did that.
    dates are stored as numbers - and a number is a number as far as format is concerned.

  15. #15
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Since the field is the result of a calculation each day could commulative rounding error come into play?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Formatting Number in text field
    By MTSPEER in forum Queries
    Replies: 1
    Last Post: 05-11-2017, 01:13 PM
  2. Conditional number formatting
    By numberguy in forum Reports
    Replies: 6
    Last Post: 06-19-2014, 02:55 PM
  3. Chart, keeping number formatting from query
    By Gabriel2012 in forum Reports
    Replies: 3
    Last Post: 12-20-2012, 11:36 AM
  4. Number formatting issues in VBA.
    By Fish218 in forum Forms
    Replies: 1
    Last Post: 03-09-2012, 12:34 PM
  5. Formatting a number field for datasheet view
    By RamiMohaisen in forum Access
    Replies: 3
    Last Post: 02-08-2012, 05:46 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