calculating Week numbers in a time table

[Bob Quintal]

=?Utf-8?B?SGlsYXJ5cw==?= <[email protected]>
wrote in

I have a weekly timetable table for school terms with the
following fields WeekID (automatic number), WkbeginDate
(date), WkNo ( number 1 or 2). The WkNo can be either 1 or 2 .
How can I get the Wkno field to automatically update with the
correct week number for each new record ie if a record has
week value 2 the next record will have a WkNo value of 1? I
would prefer to do this using a query rather than a form.
The timetable is not for the whole year and has 'holiday'
breaks, sometimes restarting on a week 2, sometimes on a week
1. Hope this makes sense!
Hilarys

It is much easier to do things like this in a form, A form can
be made to look just like a query. Please reconsider.

To do this in a query, you could fix a term start date, in a
separate table with only 1 row.

add this table to your query along with your existing timetable.

datediff("WW",termstartdate,weekbegindate) will return the
number of weeks since term began.

This number mod 2 will return a 0 or a 1. to which you will add
1 to get your wkno.

but that won't work when adding new rows to the table, so after
you add one you will have to rerun the query.

 

[Guest]

I have a weekly timetable table for school terms with the following fields
WeekID (automatic number), WkbeginDate (date), WkNo ( number 1 or 2). The
WkNo can be either 1 or 2 . How can I get the Wkno field to automatically
update with the correct week number for each new record ie if a record has
week value 2 the next record will have a WkNo value of 1? I would prefer to
do this using a query rather than a form.
The timetable is not for the whole year and has 'holiday' breaks, sometimes
restarting on a week 2, sometimes on a week 1.
Hope this makes sense!
Hilarys

 

[Guest]

Hi,

I can't see that you can calculate the current week details. If you can't
my suggestion would be to make a table showing every sunday for the next (Say
5 Years or whatever you want manually set the Week No) then when you want the
current week no 1 or 2 you could do either:-

1) Dlookup

or

2) Write a module which would find the current week no

If you need help on any of the above please ask.

Regards

Trev