Week Banding - Mon - Sun

[CJ]

Hi,

I have several Access Queries that take Order and group it into the week band it belongs too.
I.E 06/11/2015 would fall into 02/11/2015 - 08/11/2015
12/11/2015 would fall into 09/11/2015 - 15/11/2015
Which I do with a Linked Excel look up Table which has every date of the year and it's corresponding Week Group.

I'm just about to add 2016 dates & Week Groups to this Table.

And had a thought has anyone got a Access a IIf statement or any VBA that can do the above when the query is run rather than using a Look up table.

Chris

 

[mole999]

in a field
DatePart(“ww”,Date())

i think

 

[HiTechCoach]

Adding to molw999's suggestion:

If you will be running the report for dates that span multiple years then you will need to add the year.

Example:

? Format(Date,"yyyy-ww")
2015-45

In the report's record source (query) add a calculated field to hold the week. I will Assume the field you want to use is called [OrderDate]

Example:

... Format([OrderDate],"yyyy-ww") As OrderWeek

Now you can group on OrderWeek

You can even calculate the week date range from an Order Date

The first day of the current week (assuming Sunday = day 1):

Date() - WeekDay(Date()) + 1

The last day of the current week:

 Date() - WeekDay(Date()) + 7

In your group header for grouping on OrderWeek you can set eh control source for a text box to be:

=Format([OrderDate] - WeekDay([OrderDate]) + 1, "mm/dd/yyyy) & " to " & Format( [OrderDate] - WeekDay([OrderDate]) + 7 ,"mm/dd/yyyy")

 

[CJ]

Thanks...

The final piece of your reply gives me exactly what I want, except it starts on Sun instead of Mon and the week band dates out correspondingly, if this is fixed will it fix the weekband dates as well, if not I have fixed it see end of reply..

Your code gives me this, (I changed the Format to UK dd/mm/yyyy)
The below weekbands should be 02/11/2015 to 08/11/2015

Date WeekEnd/WeekDay2 Day WeekEnd/WeekDay3 WeekBand
01/11/2015 1 Sun Weekend 01/11/2015 to 07/11/2015
02/11/2015 2 Mon Weekday 01/11/2015 to 07/11/2015
03/11/2015 3 Tue Weekday 01/11/2015 to 07/11/2015
04/11/2015 4 Wed Weekday 01/11/2015 to 07/11/2015
05/11/2015 5 Thu Weekday 01/11/2015 to 07/11/2015
06/11/2015 6 Fri Weekday 01/11/2015 to 07/11/2015
07/11/2015 7 Sat Weekend 01/11/2015 to 07/11/2015
08/11/2015 1 Sun Weekend 08/11/2015 to 14/11/2015

The week is starting on a Sunday and the weekband dates are one day out

I can fix the week banding dates with the below

WeekBand: Format([OrderDate]-Weekday([OrderDate])+2,"dd/mm/yyyy") & " to " & Format([OrderDate]-Weekday([Date])+8,"dd/mm/yyyy")

Date WeekEnd/WeekDay2 Day WeekEnd/WeekDay3 WeekBand
01/11/2015 1 Sun Weekend 02/11/2015 to 08/11/2015
02/11/2015 2 Mon Weekday 02/11/2015 to 08/11/2015
03/11/2015 3 Tue Weekday 02/11/2015 to 08/11/2015
04/11/2015 4 Wed Weekday 02/11/2015 to 08/11/2015
05/11/2015 5 Thu Weekday 02/11/2015 to 08/11/2015
06/11/2015 6 Fri Weekday 02/11/2015 to 08/11/2015
07/11/2015 7 Sat Weekend 02/11/2015 to 08/11/2015
08/11/2015 1 Sun Weekend 09/11/2015 to 15/11/2015

 

[HiTechCoach]

Thanks...

The final piece of your reply gives me exactly what I want, except it starts on Sun instead of Mon and the week band dates out correspondingly, if this is fixed will it fix the weekband dates as well, if not I have fixed it see end of reply..

Your code gives me this, (I changed the Format to UK dd/mm/yyyy)
The below weekbands should be 02/11/2015 to 08/11/2015

Sure. Change the date format as needed.

The week is starting on a Sunday and the weekband dates are one day out

I can fix the week banding dates with the below

WeekBand: Format([OrderDate]-Weekday([OrderDate])+2,"dd/mm/yyyy") & " to " & Format([OrderDate]-Weekday([Date])+8,"dd/mm/yyyy")

That is it. Adjust the calculations as need for your start and end week days.