Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2008

    Unanswered: Date/day related calculation

    Hi there - hope somebody can help a newbie. I've set up an Access 2003 database to handle driver's hours for a small transport company. On a form for input I have a date field, and another field showing the day of the week from the "dddd" format of the date. I also have a field where the number of hours worked that day is entered.

    I would like to be able to calculate and show the number of hours worked in the last week (always Monday to Sunday) and during the current week. For example - if today is Wednesday, then one field [lastweek] should show the total hours of Monday-Sunday last week and in another field [thisweek] the total hours for Monday and Tuesday of the current week. My problem is that I don't have any experience of macro or VBA coding, and the fact that the calculation for both fields will change depending on which day of the week it is in the current week.

    Can anybody help?
    Last edited by pkstormy; 07-25-08 at 20:06.

  2. #2
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 5
    First thing you need to is Sum the Hours works by weekend date
    Function MyWeekEndDate(dat) As Date
    Dim Daydiff
    If IsNull(dat) Then Exit Function
    ' This Will return a Weekending Date
    ' Must set the Daydiff =
    ' 0 = Sat , 6 = Sun
    ' 5 = Mon , 4 = Tue
    ' 3 = Wed , 2 = Thu
    ' 1 = Fri
    ' Setting Daydiff to to a Above day Should Return that Date in
    ' the week in question
    Daydiff = 6
    dat = DateSerial(Year(dat), Month(dat), Day(dat))
    If dat Mod 7 > 0 Then
    MyWeekEndDate = (dat - dat Mod 7 + 7) - Daydiff
    MyWeekEndDate = dat - Daydiff
    End If
    End Function
    copy the above code in a module

    know create a new query

    then weekend:MyWeekEndDate([thedatefeild])
    run it

    weekend feild be should be showing you all sundays dates base on the [thedatefeild]
    getting Closer to the goal

    so all we need to now is groupit by the Weekend feild and sum hours worked
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

Posting Permissions

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