Need to compare week of year to current week without confusing weeks in next year with this year

[Reaper]

I'm creating a PM scheduling database. Each of the PM's will be scheduled by a week of the year. Whenever a PM is completed it should calculate the next PM due based on the time frame, i.e., weekly, 4-week, 13-week, 26-week or 52-week. An issue arises when the next PM due rolls over to a new year. Since there is no "year" data incorporated into the week number, the weeks will start over again at 1. This will be a problem when figuring out if any PM events are due or late. For example, if the next PM is due in week 2 of 2016 and the current week is 26, comparing the week number to the current week will show this PM actually due in 2016 as past due.

Any ideas on how to accomplish the task here?

[June7]

Then include the year in the identifier, like YYYYWW.

Or maybe instead of week number, calculate first date in week as the due value. This provides an actual date value to work with as opposed to YYYYWW, which can be saved as a number or string but is not a date value.

[Missinglinq]

I agree with June7; there's no real way you can do this kind of thing without including the year component. Aside from knowing when the next PM is due, one would think that you'd want to retain a record of each time a PM was done.

The Date handling functions in Access VBA are really powerful and easy to utilize. A number of years ago I wrote a database using the language that VB and VBA was based on, QuickBasic 4.5, and had to do exactly what you're doing here...scheduling, in my case, follow-up home care visits that had variable frequencies. The code for doing this, and taking into account when the projected date went into the next year, if I remember correctly, about three pages of code; in Access, with DateAdd(), it would only take a single line of code!

Linq ;0)>

[Reaper]

Thanks for your help. Pretty much what I had concluded. I haven't had to work much with this exact problem before. Actually having the full date as June7 proposed may be more user friendly than just having the week number.