Mapping week numbers and dates

[kerrance76]

Hello thank you for reading my question

I am setting up a database for a training centre, part of this includes registration
The academic year I am working with runs over 36 weeks with 24 teaching weeks, 2 x 3 week holidays, 2 x 1 week reading weeks and the final 2 weeks for exams.

I aim to set the system up so that when a user opens the application, I get the username from windows, the day and time then open the correct register. That bit should be fine.

As I have 36 weeks I want to some how map the week numbers to the dates at the start of the term and also the holidays. There are 2 reasons for this, the first being so that when the user logs in the register only shows the current week instead of needing to search along all 24 or 36 entries, the other reason is so that when checking the whole register the holidays are not shown.

Does anybody have any suggestions or pointers as to how I can do this?

Thanks

 

[wazz]

in the Control Source of an unbound textbox:
- you can calculate the current week with: =Format(Now(),"ww")
- you can calculate the week of a date entered in another textbox with: =Format([Text2],"ww")

- i had to do something like what you are doing and i found that the easiest way was to just include a field called: WeekNum.
- our "season" starts in mid-august making it tricky - or not practical enough to bother - to do date/time calculations when week 1 does not match with jan 1.
- i think i used a table called tblSeason with fields like SeasonStartYear and a table called tblWeek with fields like WeekStartDate and WeekEndDate (end date not entirely necessary). you can add a WeekNum field or create a query that counts the number of weeks. as i recall, the WeekNum field had to be two digits so that week 12 would be after week 11 and not after week 1. (IOW, week 01, 02, 03, ... not week 1, 2, 3...).

hth.

 

[kerrance76]

sorry for my delay in replying i have been thinking about your reply.

I have a link table between student and modules tables with week 1 - 24 as some of the fields, if I've got this right i can enter the 'year' start date in another table, which will be September or October, then i can get the week number of that date from January, this will be my offset. i can then get the current week number and subtract the offset from it, this will give me the week number i need to use in a query or filter to display the current week on the form.
After the new year i then need to change this calculation.

And using the two pieces of code you have given will give me the week numbers