week numbers

[ukmale65000]

I currently have a box on a form to show the week number, I have added ww to the format and indeed the box on the form populates, however it is showing 53, what i really need is it to display the proper week number given that our year starts on the 5th of April. As an absolute novice, im not sure where to start putting this right, however i am hoping someone can point me in the right direction.
Thanks

[orange]

see http://www.techonthenet.com/access/f...e/datepart.php for week numbers.

You might also want to google "fiscal year", and 'Fiscal Year in msAccess'

see also http://support.microsoft.com/kb/210249
and/or http://www.youtube.com/watch?v=Nb_pi1wplz0

[ukmale65000]

see http://www.techonthenet.com/access/f...e/datepart.php for week numbers.

You might also want to google "fiscal year", and 'Fiscal Year in msAccess'

see also http://support.microsoft.com/kb/210249
and/or http://www.youtube.com/watch?v=Nb_pi1wplz0

Thanks for the links but they dont seem to do what i want, basically if i open my database on the 5th of april 2014, i want the week number to show week 1, if i open it on the 13th April it should show week two and so on. Im not sure if Access can pull the current date from the computer somehow and then display the correct week number given the criteria that week 1 starts on the 5th of April each year.
As i have said in my first post, im not that savvy, but trying to learn

Thanks

[ItsMe]

I don't know that this is very simple to calculate. I built you a DB to count weeks starting from April 5th. When you open it, the DB will prompt you to enter a date. It first needs to poulate a table with a RANGE of dates.

Enter 1-1-2012 and then 1200 days if you plan to query all of last year and a couple years into the future.

After the table is populated, you can query a date and it will determine the week number for that date. However, it may count 4/5 as week number one in instances you do not want. A new (2nd) week will begin on the very next Sunday.


edit: I just noticed I made a mistake in the original DB. I have to add a couple lines of code.

.April5_Revised1.zip
OK, I changed things around. It will count April 5 as the beginning of the week. If April 5 is last calendar year then it starts last calendar year. Previous version only worked on calander year. April5_Revised1 is attached above

[ItsMe]

I made some changes and uploaded another DB file. First one had some bugs.

[ukmale65000]

I made some changes and uploaded another DB file. First one had some bugs.

Hi thanks for that and your db works absolutely fine, just need to know how i can incorporate it into my db. If im honest what i would like to do is somehow extract what you have written so that when i open my form, my week number box is populated simply by the fact that access knows what date it is from my computer. Do you know if this is possible using the information you have already supplied ?

[ItsMe]

Yes, it is possible. I think before you incorporate it with your DB you should consider the business rules that decide how you count the weeks. You need to make sure that the code I wrote is doing it correctly. For instance, will you always count April 5 as week #1, regardless of it falling on Saturday or on a Monday? Does April 5 mandate the beginning of a week? Do you count a full 7 days before week two begins?

Just not sure what you are trying to accomplish.

[ukmale65000]

Hi yes april 5th is always day one of week one. What im trying to acheive is autopopulating a box on a form with the current week number using the paramaters i have written above. Im a novice so am feeling my way round creating the database. I have tried various vba codes within the onclick function of the week number box but am getting nowhere. I can attach the db if needed
Thanks

[ItsMe]

Let's say you have a date of March 10, 2012. All you need to do is count the number of days March 10, 2012 is from April 5, 2011 and then divide that result by 7?

The result you are looking for has nothing to do with weekdays or calendar months?

[ukmale65000]

Ok i understand what your saying now i need to figure out how to acheive this and where to incorporate it into my db
thanks

[ItsMe]

If you share the business rules with me I may be able to help. Take a look at the DB I provided. Look at the table inside the DB.

The DB asks you to populate the table so it will have a way to look up any weekday for the given date range. This is how the DB can count the weeks. Perhaps looking at the table will help you to determine what YOUR business rules are.

It seems odd to me that you have a date as a beginning week. It is an oxymoron if you are to apply this to more than one calendar year.

[ukmale65000]

The date used is the beginning of the uk tax year always the same but i thought if i could work out the vba code it would enable me to enter any date as the start of year date.
Ive tried the following as an onclick command but it dosent work
Private sub weeknumber_click()

Dim lvalue as interger
Lvalue = datepart("ww", [vbsunday], [vbfirstapr5])

End sub

[ItsMe]

Well, I can appreciate the fact that you have to report earnings and expenses between 4/5 and 4/4. That is a new one to me....

I am not sure how you can count periods from this. I will have to take another look at it. The problem is that you are trying to cram one calendar into another. The units of measurements do not match. Work weeks don't begin on April 5. Maybe once in a blue moon they do, but you need something that will work every year.

Obviously this is not a new accounting problem. Isn't there something accounts are doing to count periods? It just causes me to keep going back to the business rules. I can get code to work out a formula. I simply need to understand the formula first. All the fiscal years I deal with begin on the first. I count periods the same way, each time, regardless of the month the year starts in.

[ukmale65000]

Ok for whatever reason uk financial year starts on 6th april (not 5th) as i said earlier so ideally this would be day 1 week 1 however if its easier im happy for 1st jan to be start of week 1. Im just desperate to work out if this is possible and to learn how its done. It will take a brain far better than mine i think lol

[ItsMe]

Your result is going to have a partial "week". What do you want to do with the remainder?

The 4th, 5th, 6th, or the 1st - take your pick. Any choice, the "date" is not always going to start on the same "weekday". Take a look at the table in the DB I uploaded. Periods are usually counted in months or quarters, not weeks.

Before you can look at VBA code, a data base, a textbox, a form... you need to know HOW you are going to count the week. Weeks start on Sunday and end on Saturday.

If all you want to do is count the number of Sundays, the DB I uploaded will count the number of Sundays between two dates. That is what it does. It counts weeks. You still need to decide what to do with the remainder. The DB I provided does not know how to present the remainder. It does not know the business rule. If you know, let me know. I will tell the DB.