Program Year vs Calendar Year - Tracking the weeks

Program Year vs Calendar Year - Tracking the weeks

Post by Simon Jest » Sat, 05 May 2007 09:53:40


I ran across a curious situation that someone who knew what they were
doing would probably be able to solve without even thinking about it.
I have to track some data about services delivered to our customers on
a weekly basis. I started off with a parameter query and asked for
the occurrence of each service between the date the week began and the
date the week ended. This was fine until I discovered that some
services were being entered and back-dated (perfectly legit but it
causes my numbers for previous weeks to be off). So I got this
brilliant idea to create a query that breaks down the services by each
week of the entire month. CalWeekNum: =Format([SERVICEDTE],"ww").
This way I won't have to run the query for each week looking for
numbers that might have changed. Works like a charm. Unfortunately,
everything I do is on a Program year - July to June. So I tried
another calculation: PYWeekNum:=Format([SERVICEDTE],"ww")-26. This
too works like a charm until you get to the 27th week and it starts
numbering the weeks backwards: 25, 26, -26,-25. Gets very confusing.
Does anyone know of a better way of getting the number of the PY week?
Any help is much appreciated.
Thanks much!
SJ
 
 
 

Program Year vs Calendar Year - Tracking the weeks

Post by RHVhbmUgSG » Sat, 05 May 2007 11:26:03

How about something like:
PYWeekNum:=Format(DateAdd("m",-6,[SERVICEDTE]),"ww")
--
Duane Hookom
Microsoft Access MVP