Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2007
    Posts
    1

    Question Unanswered: Access 2000 - Calculating times in tables

    How do you get access to calculate time in an access table? I need that to be a field in a report to be a field that can be sorted.


    Fields:
    Start Time
    End Time
    Lunch Time


    I need it to be like this

    =End Time Start Time Lunch Time

    Thanks
    Andy

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Rule numero uno: never put a calculated field in a table, IF you can calculate said field from other fields within the table.

    If Start Time, End Time, and Lunch Time all exist in each record of your table, leave calculations for forms, queries or reports.

    In your report, you can have a control named TotalTime, the Control Source of which can be the formula you put forth.

    Caution, though. Start time, end time, and lunch time all must be Date type fields, or double numerics. If you're using string type fields, you must use the CDate() function before you can do any math on those fields.

    HTH,
    Sam

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    ummm.....,

    never mind.

    I won't stir up trouble with my thoughts on storing the calculated value.
    Last edited by pkstormy; 08-07-07 at 21:34.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the date / time function in the help file...
    I'm guessing that datediff is going to be fairly appropriate for this task.

    Paul ID agree that sometime storing derived data is appropriate.. and this may be one of them.

    incidentally I woudl have expected to see something like

    Start Time
    End Time
    Lunch Time

    to be
    clockonTime
    clockoffTime

    and each user generates as many records as he wants/needs or whatever
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Paul, healdem,

    I was only addressing the inefficiency of storing the calculated hours in the detail table. I was not addressing whether or not the value should be stored in the master table. If management is computer savvy, and they want to peruse the actual table, I would argue to put it there. However, if all management wants to see is a paper report, I'd rather save the trouble and leave the calc for the report design.

    Sam

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Are the Start and End times absolute times (like 11:05:32 or something)? Is the lunch time a difference in time (like 54:23)? If so, I'd have to agree with healdem; you ought to have just a table of ons and offs. Things get weird when you're calculating fields that are storing different types of data IMHO.
    Me.Geek = True

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by Sam Landy
    Paul, healdem,

    I was only addressing the inefficiency of storing the calculated hours in the detail table. I was not addressing whether or not the value should be stored in the master table. If management is computer savvy, and they want to peruse the actual table, I would argue to put it there. However, if all management wants to see is a paper report, I'd rather save the trouble and leave the calc for the report design.

    Sam
    I know Sam. I've got to stop being stubborn on the calculated value thing. I think I work with too many accounting db's where I almost always need to store the calculated value because I need to do averages and other calculations on that field and need fast returns for the forms/reports. I've gotten into the habit of storing it to make things easier but I know there are different opinions on this.
    Last edited by pkstormy; 08-08-07 at 22:12.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pkstormy
    ummm.....,

    never mind.

    I won't stir up trouble with my thoughts on storing the calculated value.
    Heh - I saw the title and was about to post something just about the same
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yep, I'm not going to bring up this arguement again either!
    George
    Home | Blog

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks for going easy on me guys.
    Last edited by pkstormy; 08-09-07 at 13:49.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't think I have a problem with LunchTime being in minutes (or whatever unit it is). So long as it is fixed and he isn't intending to input the actual number of minutes spent each lunch by each person. In that case start and end defo. Depends on the business requirement - if it is to assume that everyone takes a full lunch break and no more and just knock it off the total time working why not?

    I would change the name to something like lunch_len_mins so as not to confuse with the time fields.

    ...and if it is fixed it should also be split off to a different table.

    Probably all academic - I think aharsh has scarpered anyway.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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