Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Unanswered: Format to equal HH:MM:SS

    I am using IBM DB2 v7.2 on Server 2003 using Access '97 (has to be '97 due to the program that it runs with). I am in need of making the minutes (which look like such(12.9877)) to equal HH:MM:ss
    This is what I have come up with in the Reports field but still I am unable to get a correct result: Avg([FFF])&Format([00\:00\:00])
    Also the FFF is pointing to the Queries and is set to: FFF: DateDiff("n",[TEST_PERFORMED],[TEST_ACQUIRED])
    I have tried many diff options in rewriting this to work, so any ideas are welcome...
    Thanks
    Krvin

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    And 12.9877 is what? 12.9877 hours into the day??
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jul 2009
    Posts
    7

    And 12.9877 is what? 12.9877 hours into the day??

    12.9877 = 12hours + .9877 of an hour

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, so you can convert those hours to days and THEN apply a date format:

    =Format(FFF/24,"h:nn:ss")
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jul 2009
    Posts
    7
    Is this how you are stating it should now look?
    Avg([FFF])=Format(FFF/24,"h:nn:ss")

    Thank you for all your help, and I am sorry for not responding quicker on this.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No.

    =Format(FFF/24,"h:nn:ss")

    or maybe

    =Format(Avg(FFF)/24,"h:nn:ss")

    The specifics kinda depend on where exactly the expression is placed... a query column or a form/report control.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Jul 2009
    Posts
    7

    Format to equal HH:MM:SS

    Ok I am using this =Format(Avg([FFF])/24,"h:nn")
    It is correct calculated time, however I am unable to get it to produce hours:minutes instead it gets the following results:

    Test Performed Test Recieved Calculated Difference HH:MM
    04/01/2009 11:51:22 04/01/2009 11:57:48 0:06
    04/01/2009 01:47:56 04/01/2009 01:55:00 0:08
    Overall Average: 7:00


    Once again Thank You for all your help. This has been great learning for me on this project with everyones help.
    Kevin

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I assume it's the overall average you are referring to.... Does the control have a format set as well? You might need to remove that.
    Last edited by StarTrekker; 08-27-09 at 20:40.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •