Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calcs and format question (XP)

    Hello,

    How can I set a field property to accept a format such as 53:56:10 (53 hours, 56 minutes, 10 seconds) in a table and be able to perform a calculation (avg) in a report? Right now these fields are set to text and I just type the digits in the above format. I'm getting an "expression is too complex" error when I try to run the report.

    The big picture is that I am trying to average out the number of hours over the course of several weeks. Each week is a record and the report is based on a parameter query which works fine.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calcs and format question (XP)

    Unlike Excel, Access doesn't support times over 24 hours (Excel has a [hh]:mm:ss format that can display 53:56:10)

    You could use three separate number fields for hours, minutes and seconds, or a separate number field for the number of days, plus a time field for times up to 23:59:59.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calcs and format question (XP)

    Thanks, Hans. Neither of these options are plausble. Can Access calculate text fields? (My gut says no for the obvious reason that text isn't number, but what gets me is that it was working when I inherited this file).

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calcs and format question (XP)

    You will have to write expressions or VBA code to parse the text field into parts that Access can handle, then perform the calculations, and finally transform the result back into a text string with the desired display format. This can be done, but it will be LOTS slower than using the built-in date/time data type.

    For example, you could do all calculations in seconds. If you have a text field TimeText that contains (for example) "53:56:10", you can convert this to seconds:

    CLng(Left([TimeText], InStr([TimeText], ":")-1))*3600 + CLng(Mid([TimeText], InStr([TimeText], ":")+1, 2))*60 + CLng(Right([TimeText], 2))

    <img src=/S/flee.gif border=0 alt=flee width=25 height=25>

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calcs and format question (XP)

    Thanks, Hans. I probably will just have them export the data to Excel and calculate averages there.

Posting Permissions

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