Displaying Week Number Based on Date Range

[DavidLomax]

Hi, I'm building my first database for a friend's company and have a question. The company uses their own system for numbering weeks, so for example this week (Nov 21-27) is week no. 366, the following week will be week no 367 and so on.

I've built a Weeks table where the week number and the particular date range for that week is inputed (along with other things they need to define for every week).

What my friend wants is to have a permanent box on top of every layout that shows today's date (solved that by using get currentdate) but also the number of the current week.

How do I define the field that it works? I tried just linking that field to the weekNumber field in the Weeks table (all the layouts are related, though not all directly) and it doesn't work, it shows a random week number on some layouts and stays blank on others. I assume because there are several weeks already setup and the DB doesn't know which one to show...?

I guess I could build a Case calculation (if the date is in this range, display this number, etc), but that is not very practical as I would have to keep updating the calculation as time goes on and there would be one such calculation for every layout. Plus it seems like I should be able to display that since there is a table for setting up the week numbers.

Thanks for any help.

[Vaughan]

The company uses their own system for numbering weeks, so for example this week (Nov 21-27) is week no. 366, the following week will be week no 367 and so on

Does this mean that they had a "week 1" and all weeks are numbered sequentially after that? If so, then I suggest you work out the algorithm to calculate the week number (it is pretty simple) and use this to calculate the week number from the current date. Make it an unstored calculation and put it in every table that has a layout in the main interface.

I've built a Weeks table where the week number and the particular date range for that week is inputed (along with other things they need to define for every week).

What kind of things do they need to define every week? Have they invented new week names as well?

I'd be encouraging them to standardise on what the rest of the civilised world uses: or or failing that, what the US uses. :(

[comment]

What my friend wants is to have a permanent box on top of every layout that shows today's date (solved that by using get currentdate) but also the number of the current week.

Try =

Div ( Get ( CurrentDate ) ; 7 ) - 104504

This must be an ustored calculation, with the result type set to Number.