Week Commencing

[Terry Holland]

I need to write a query that totals hours for a week and I need to show the
'week commencing' date for each of the weeks.
eg for last week I might have the following data

Mon 15 Nov : 4 Hrs
Tue 16 Nov : 5 Hrs
Wed 17 Nov : 9 Hrs
Thu 18 Nov : 3 Hrs
Fri 19 Nov : 2 Hrs
Total for week commencing Mon 15 Nov = 23 Hours

Can someone point me in the right direction for a query that will do this


tia

 

[Arvin Meyer]

First you need a function to get the start of the week:

Function WeekOf(dtmIn) As Date
On Error Resume Next
If IsDate(dtmIn) Then
WeekOf = DateAdd("d", (2 - (Weekday(dtmIn))), dtmIn)
Else
Exit Function
End If
End Function


Then you'll run a query something like:

SELECT WeekOf([DateField]) AS WeekOf, Sum(Table1.Test) AS Total
FROM Table1
GROUP BY WeekOf([DateField]);
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Terry Holland]

Thanks

I had come up with this

Function WeekCommencing(dteDate As Date) As Date
'Function will return the Week Commencing date
'for the date entered.
'Assumptions:
' A week commences on a Monday

Dim dteRetVal As Date
Dim intDay As Integer
intDay = (Weekday(dteDate) + 5) Mod 7

dteRetVal = dteDate - intDay

WeekCommencing = Format(dteRetVal, "Short Date")

End Function