1. Registered User
Join Date
Jul 2008
Posts
1

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 21:06.

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
First thing you need to is Sum the Hours works by weekend date
Code:
```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
Else
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

#### Posting Permissions

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