If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: weekly pay id generated from current date

Hi there
I'm new to the database field and i am trying to setup a timesheet database for our company, the old one is very painful so i'm trying to update it.

our timesheets run from wednesday to wednesday and for each week in the month we use the following code - Aug09-1 = week 1, Aug09-2 = week2 etc.
i am trying to figure out a formula that will generate this automatically for me using the current date and would create a new code each week ready to be used. the figure would be stored in the database so it can be used in reports, billing and employee payments.
basically payweeks are labelled accoring to the wednesday they start on.
the week that runs from wed sept 30 to tues oct 6 this would be Sep09-5

currently the guys in the workshop have to manually put enter this in and they often get it wrong.
i hope i have supplied enough info.
cheers
Aaron

If you have a hard and unbending rule that governs week one of your financial year, I have a function (based on the ISO standards) that will calculate the start date of a year. There are other functions that go with it to calculate week numbers and (if you use them and have similar rules governing them) period numbers. The start of year function is currently set to work from a Sunday, so a little tweaking would be required.

(BTW, I'm sure you meant that your timesheets run from Wednesday to Tuesday, unless every Wednesday is counted in two weeks...)

Without using complex coding, I would do the following:
Create a table with fields for the start of each week, the finish of each week, and the week number e.g. 07/10/2009,13/10/2009, OCT09-1.

Using the above table, use code to store each date and week number in another table e.g. 07/10/2009 OCT09-1, 08/10/2009 OCT09-1 etc. This could be done for the whole year or each month for the next month etc.

You then would use DLOOKUP to lookup the week number based on the date entered.

Using the above method would reduce the need to create complex coding and spend time testing for various conditions.

that what the weekend function does
you should be able to put any date in the weekend and it will pass the wedday of that date in question

Not fully tested yet.

this should point you down the right trace

just copy the above code into a module

to use in a Query PAYWEEK:Payweek(feildname)

in code
me.feildname = PayWeek(date())

hope this help

StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010VB-NET based on my own environment started 2007SQL-2005 based on my own environment started 2008YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON

Myle your code works for the week of the month.
The requirement is that Wednesday is the first day of the working week so 7th October would be week 1 e.g. OCT09-1, but the 6th October would be in the fith week of September e.g. SEP09-5

poppa smurf, thanks
your code works great, now i just need to work out how to apply it to my timesheet system so that when i tech enters the date it generates the payweekid and puts in in the correct table.

The attached updated version maybe useful I created the code as function. When you open the form the current date and the Week reference is displayed. When you enter a date in the text box the week reference for the date entered is displayed.