Creating wk1 as starting wk for april 1st

[kevin19]

Hi All!
Does anyone know the best solution to create april 1st as the commencing week for the year instead of week 1 beginning from 1st January?
I need to have the week numbers to relate properly to that particular financial year automatically.

Thanks!

 

[Big Pat]

This is how I do it.

Start in Excel, with two columns, WeekNo and WeekCommencing. In col A, inssert the numbers 1-52 (or possibly 53). In B2 insert 01/04/2013, under it, enter =B2+7. Copy that formula all the way down.

You now have two columns with week numbers and dates. Save and close the file. Go to Access and import the new table. In Access 07 it's something like External Data > Import > Select file. In Access 03 it's Database Window > Tables > New > Import.

For future reference I would also have a FinancialYear column in my table and make it a lot longer e.g. 5 years.

 

[kevin19]

Thanks Big Pat for your prompt response and help. Maybe I am not very good at explaining. What I am trying to do is that if I enter a date in the date field and format it as wk. no. it will automatically comes out that specific week number relating to that date.
E.G. If I enter 01/04/13 into my date field, it will automatically shows 1 instead of the date itself.
I know it is possible but I do not know how to do it. Writing code, etc.
Maybe someone has the code and can advise or teach me??

 

[Big Pat]

OK, I see what you mean. You'll still need a table of dates, because the date you enter will need to refer to that table to get the week number.

See the attached sample database. Take a look at the green box when you open Form1 in design view

 

[kevin19]

Thanks Big Pat! Your help is greatly appreciated! What more can I say! You solved my problem that has been bugging me for weeks.
Thanks again for your Big help!!!

 

[kevin19]

Hi big Pat!
Maybe you can further assist me in this as you managed to help me greatly with the wk. number thingy....
Now I have a few weeks record showing when I open my form.
I want to see just the current week so what is the best method to adopt?
Basically, every week when I open my form I just want to see the current week records from Mondays to Sundays only.
thanks once again!!