Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Location
    Grantham, England
    Posts
    8

    Angry Unanswered: MS ACCESS Format with fractions of seconds

    I am trying to create a table of Race Track Times which involve minutes, seconds & fractions of seconds e.g. 2:13.78 . Access does not seem to handle this easily, or am I missing something?

    I would be grateful for any help.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Check the Access help system for these functions:

    The Time() function and the Format function.


  3. #3
    Join Date
    Mar 2004
    Location
    Grantham, England
    Posts
    8

    MS ACCESS Format with fractions of seconds

    Thanks for replying, CyberLynx, but I have already searched the helpfiles without joy.

    Access insists on using whole seconds in time calcs. I tried importing from an Excel sheet but Access created the new table with the data rounded to nearest whole second.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    what format do you have in the table (and everywhere else you are looking for fractional seconds like textboxes, reports, etc).

    you need the format: Long Time

    i guess A is not storing hours/minutes/seconds anyway, but fractional days (i.e. 0.75 = 18:00:00) with resolution down to 0.01 seconds

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Mar 2004
    Location
    Grantham, England
    Posts
    8
    THANKS FOR YOUR HELP, IZY, BUT I HAVE TRIED LONG TIME FORMAT. IF I THEN ENTER , FOR EXAMPLE, 2 HRS 32MINS 12.23 SECS AS 02:32:12.23 ACCESS BRINGS UP AN ERROR MESSAGE REFERRING TO AN INVALID ENTRY FOR FIELD.

    I HAVE RECEIVED THE FOLLOWING ADVICE ELSEWHERE:

    "I would make the field a text box with an inut mask to be used where you enter the data. Also include validation to validate the data when you enter it.

    To do any caclulations with this data you will have to use code, pull it apart and perform your calculations and then put it back together."

    THIS IS NOT IDEAL FOR A NOVICE USER, I WOULD HAVE THOUGHT THAT THERE WAS A SIMPLER SOLUTION ALTHOUGH I AM SURE THAT THIS IS CORRECT ADVICE. EXCEL HANDLES FRACTIONS OF SECONDS EASILY.

    THANKS AGAIN,

    RIC

  6. #6
    Join Date
    Dec 2001
    Posts
    79
    We have a freeware module that might help:

    Custom Time Functions
    http://www.peterssoftware.com/ctf.htm

    Custom Time Functions are a collection of functions that you can use in your Access application to perform mathematical operations on custom time formats. Included are functions to add, subtract, multiply, and divide time variables that include time units below the value of seconds. Use Custom Time Functions to multiply and divide milisecond values, find the total length of a film plus any film trailers down to the level of frames per second, and more with this handy module.
    Peter De Baets
    Peter's Software - Microsoft Access Tools for Developers
    http://www.peterssoftware.com

  7. #7
    Join Date
    Mar 2004
    Location
    Grantham, England
    Posts
    8
    Thanks for your link Peter, I will let you know how I get on with it.

  8. #8
    Join Date
    Mar 2004
    Location
    Grantham, England
    Posts
    8
    Hi Peter (pdebaets),

    Tried Custom Time Function, thank you very much, but it doesn't solve my problem as it is limited to unbound text boxes, I believe. My problem involves the entering of a fair amount of data into fields; I don't think that your function can be used this way. Maybe I am wrong but thanks again anyway.

  9. #9
    Join Date
    Nov 2003
    Posts
    1,487
    To do what you want, I think perhaps making the table field a String field might be the way to go, then convert it or manipulate it as required via code when the time arises to do so.

    For example, a time field entry such as 2:13.78 (2 minutes and 13.78 seconds) and another time field entry such as 2:8.12 (2 minutes and 8.12 seconds) might be worked out in such a manner:

    Get the difference between times:
    Code:
    Dim Time1 As String
    Dim Time2 As String
    Dim TMinutes1 As Integer
    Dim TSeconds1 As Single
    Dim TMinutes2 As Integer
    Dim TSeconds2 As Single
    Dim TheDifference As String
    
    Time1 = "2:13.78"
    Time2 = 2:8.12"
    
    TMinutes1 = CInt(Left$(Time1,Instr(Time1, ":") - 1))
    TSeconds1 = CSgl(Mid$(Time1,Instr(Time1, ":") + 1, Len(Time1)))
    
    TMinutes2 = CInt(Left$(Time2,Instr(Time2, ":") - 1))
    TSeconds2 = CSgl(Mid$(Time2,Instr(Time2, ":") + 1, Len(Time2)))
    
    TheDifference = "Time difference is " & CStr(TMinutes1 - TMinutes2) & " minutes and " & _
    CStr(TSeconds1 - TSeconds2) & " seconds."
    
    MsgBox TheDifference

  10. #10
    Join Date
    Mar 2004
    Location
    Grantham, England
    Posts
    8
    Thanks for your help CyberLynx. I am sure that coding is the way to go and I feel that it could get rather complicated when working with a lot of data; finding the quickest lap time from a few hundred entries would involve a lot of code. Maybe I am barking up the wrong tree. I might even have to bite the bullet and forget about the fractions of seconds or use Excel.

    Thanks again,

    Ric

  11. #11
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Indeed, Access does not support fractions of seconds. What I would do is to use FLOAT numbers to store the time in seconds from midnight. You will have to convert input data, but then, you can easley compute expressions.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  12. #12
    Join Date
    Mar 2004
    Location
    Grantham, England
    Posts
    8
    Thanks DoktorBlue,
    I think that your solution is probably the easiest to work with, i'll give it a go.

  13. #13
    Join Date
    Mar 2004
    Location
    Grantham, England
    Posts
    8
    I have spent quite some time (certainly not fractions of seconds!) considering all the advice that forum members have kindly put forward and have decided to use the following solution. This may not be ideal but it works and it is simple.

    Lap times are now entered in two fields, one for the no. of minutes and the other contains the amount of seconds to 2 decimal points. This is satisfactory both visually and for the purpose of simple input, but not for calculations. Therefore using a query, a simple expression combines the two fields into one which displays the time as seconds only. Total and/or average race times can now be calculated within a report using further expressions to display the results in two text boxes as minutes and seconds. I hope that this makes sense.

    Thanks again for all the advice.

    Ricky

Posting Permissions

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