Query to group by week, but display week commencing date (monday of each week)

[JasChima]

I have made a query in design view on Access 2000, I have made the following field which groups by the week. However it only works because i set it to format the date as "ww, mmm yy".

Field: Week of Year: format([Date], "ww", "mmm yy")
Total: Group by

Output is
Week of Year
32, Aug 04
33, Aug 04

Any suggestions on how to fix it so that it displays the date of the first Monday of each week instead?

e.g
Week Commencing
2 Aug 04
9 Aug 04

Thanks for any suggestions

 

[JimmyS]

This might be useful to use instead of your -format to week.

Here's the expression I use in a query to get the week commencing date to group by (mind you its using the Sunday)

Expr1: IIf(Weekday([yourdate])=1,[yourdate],DateSerial(Year([yourdate]),Month([yourdate]),Day([yourdate]-(Weekday([yourdate])+1))))

Otherwise do a search here for 'week commencing' and you get some vba code examples people have posted

hope this helps with ideas...

 

[Rodgeraj]

Expr1: IIf(Weekday([yourdate])=1,[yourdate],DateSerial(Year([yourdate]),Month([yourdate]),Day([yourdate]-(Weekday([yourdate])+1))))

Thanks, Jimmy. This was a great find, but what I needed it for, My week started on a Monday. Then I ran into problems with Days that were at the start of the month (like 6/1) representing themselves as for the week of 6/28. Here's my modification that accounts for Month Start Dates as well as modification for a Monday - Sunday Week.

WeekStart: IIf(Weekday([StatDate])=1,DateSerial(Year([StatDate]),Month([StatDate]),Day([StatDate])-6),IIf(Weekday([StatDate])=2,[StatDate],IIf(Day([StatDate])>7,DateSerial(Year([StatDate]),Month([StatDate]),Day([StatDate]-(Weekday([StatDate])-2))),DateSerial(Year([StatDate]),Month([StatDate]-1),Day([StatDate]-(Weekday([StatDate])-2))))))

If you've got questions, let me know.

 

[RuralGuy]

Here's a neat link for your favorites: A plethora of date functions

 

[TTTGun]

Some elegant code

I ran into the same problems that Rodgeraj found but I came up with a different solution:

WeekStart: CDbl(YourDate)-(Weekday(YourDate)-2)

This code will work regardless of the Weekday you want to start with i.e... if you want Thursday to be the first day of your week just change the 2 to a 5...Done.

Enjoy

 

[Brianwarnock]

Why not use the full functionality of Weekday

weekstart: yourdate-weekday(yourdate,vbmonday) + 1

much clearer than messing with working out the number to subtract.

Brian

 

[Brianwarnock]

RG I could not make the link you posted.

Brian

EDIt I have just noticed the dates on this thread, why did IBMfreak post to it, I feel a bit of a twit.

 

[RuralGuy]

RG I could not make the link you posted.

Brian

He changed his web site a while back.
http://www.pacificdb.com.au/Support/code_datetime.htm

 

[Brianwarnock]

Thanks RG, I wasn't surprised that there was a problem when I noticed the original date, just why people feel the need to post an agreement to a solution for a 3 year old thread beats me.

brian

 

[RuralGuy]

I have no idea either. Take care Brian.

 

[highandwild]

I hope that JasChima appreciates all of this.