Board index Office Access

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
 
 
 
Top

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

 
 
 
Top

1. Comparing y-t-d average between 2 years for each year without surpassing present year's week

2. Need to track last year to date information vs this years?

3. Mac shipments lowest in 1.5 years - first year-over-year drop in 5.5 years

4. Mac shipments lowest in 1.5 years - first year-over-year drop in 5.5 years

5. Compare this year vs. previous year data in one graph (date vs. quantity)

6. My Mac vs your Mac (Was: This year's iMac vs. last year's)

7. 13 Week Period, vs. Equivalent 13 Week Period for Prior Year

8. Programming for 14 year old (was Programming for 13 year old)

9. day of year/week of year script

10. How do I crate a function for week of year + year in same cell.

11. Outlook show week number(1-52); day of year / days left in year

12. PC Version of UT3 still on track for this year, XBLLOX version still next year!!!

13. year on year tracking

14. how do I track holiday give aways year to year

15. Track project FTE per year for five years in Access?

All times are UTC
Board index