none
[Access 2k3 + VBA + SQL] Time format not converted correctly in ListBox RRS feed

  • Question

  • Hi everybody,

    I use this SQL formula to convert time from milliseconds to minutes:seconds

    Format(TABLE1.CALLTIME/1440, 'h:nn') AS [CALL TIME]

    The issue is that, it works fine 90% of the time!

    For example:

    1666 milliseconds are converted to 3:46 instead of 27:46

    1450 milliseconds are converted to 0:10 instead of 23:10

    1958 milliseconds are converted to 8:38 instead....

    How to fix this, please?

    Regards



    • Edited by Admin-Dev Thursday, May 24, 2012 3:36 PM
    Thursday, May 24, 2012 3:35 PM

Answers

  • 1666 milliseconds is 1.666 seconds, or 00:02 minutes:seconds.

    Did you mean seconds instead of milliseconds? If so, use

    Format(TABLE1.CALLTIME/86400, 'n:ss') AS [CALL TIME]

    Explanation: dates are measured in units of 1 day. 1 day = 86400 seconds.


    Regards, Hans Vogelaar

    • Marked as answer by Admin-Dev Thursday, May 24, 2012 4:02 PM
    Thursday, May 24, 2012 3:50 PM

All replies

  • 1666 milliseconds is 1.666 seconds, or 00:02 minutes:seconds.

    Did you mean seconds instead of milliseconds? If so, use

    Format(TABLE1.CALLTIME/86400, 'n:ss') AS [CALL TIME]

    Explanation: dates are measured in units of 1 day. 1 day = 86400 seconds.


    Regards, Hans Vogelaar

    • Marked as answer by Admin-Dev Thursday, May 24, 2012 4:02 PM
    Thursday, May 24, 2012 3:50 PM
  • perfect, works like a charm!!!

    Thank you!!!

    Thursday, May 24, 2012 4:03 PM