Week Number in Report

[perryc]

Another seems simple, but with dummy VBA person like me, not sure how to do.



So, I have a set of dates (e.g. 4/10-16, 4/17-23 & 4/24-30). How can I display a Week1, Week2, Week3 in the Access reports?





Ty again.

[ArviLaanemets]

The easiest way (I think):
Create a Calendary table, which contains all dates from some start date and into some reasonable future. Into this Calendary table you can add various additional info about date - e.g. week number (My advice is to have the week number in format yyyyww).
In your report source, you join this calendary table with your datasource - then you can have week numbers matching with dates in your report.

[Micron]

Those are date ranges, not dates, so I guess datepart("ww",date) or format(date,"ww") won't work for you. Some years will return week 53, which you can work around, and probably will have to do so in a table as well.

[perryc]

Ty both. Actually, I had a initial startdate as well. So, I guess, something like this may give me the week numbers? It seems very complicated though. Is there an easier coding?

=IIf([DateAssigned] is between [StartDay]+6 and [StartDate]+13,"Wk1"...) This will last for 10wks, and it could be cumbersome to write this whole formula out. Any better suggestions?

[Micron]

Best to always show some data and sometimes even better to show expected result. No one has any real idea about what [StartDay] data looks like.
Why not just datepart("ww",date) where date is a valid date and not the date function? Regardless it would be Between, not Is Between.
Not understanding how the expression can last for 10 weeks.

If you've got something complicated going on, might be better to use a table as suggested, but I haven't seen anything here that indicates any complexity.

[perryc]

You guys are wonderful!! ty very much. This works for me.

[Micron]

This works for me.

You're welcome, but what is "this"? Posting your solution is always a good thing in that it helps others who have the same issue and end up looking here.

[Welshgasman]

I'd probably create my own function to parse that format, convert to dates and return a week number.
Hopefully the start date week is not different to end date week?