DatePart Question 2

[jon92]

I am trying to setup a staff shift rota (10 week cycle)
what I would like to be able to do is enter a date in a textbox & use DatePart("ww",[mydate]) to get the week number.

But I need to be able to link that result in [mydate] to the rota week number eg: if the result in [mydate] is 12 it would be week 2 for the shift rota.

Any assistance would be much appreciated

 

[KenReay]

Hi

The immediately obvious answer (ie use the Mod() operator to get the remainder when diving by 10, (eg 12/10, remainder = 2), unfortuanley does not work, since the number of weeks in the year is not an integral number of 10's, so in week 51, we ket week 1 of rota, week 52 week 2 of rota, but then week 1 gives us week 1 again. So the simplest way I can think is to have a table of weeknumbers vs rota weeks, (or weekending date vs rota weeks) and write yourself a function to look up the rota week number from the date.

Hope this helps

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PauloRico]

Ken was right to suggest using the Mod function.

You can get round this problem with the following -

Create a new Module and paste in the following code -

Global Const gdtmStartDate = 37712

Public Function GetRota(dtmDate As Date) As Long

Dim lngStart As Long
Dim lngDiff As Long
Dim lngCalcRota As Long

lngDiff = dtmDate - gdtmStartDate

lngCalcRota = Int((lngDiff Mod 70) / 7) + 1

GetRota = lngCalcRota

End Function

You should replace the global constant gdtmStartDate with the first date of week 1 in the rota.

Access stores dates and times as an offset from an initial date (31/12/1899 ? I think). The whole number being the date and the decimal portion being the time. In my example, 37712 equates to 01-April-2003. You can get this figure easily with the function CLng(YourDate).

 

[jon92]

Thank you both for your input, I am getting slightly out of my depth with this. Once I have created the new module do I assign it to a textbox value on a form ?

 

[jon92]

I have just worked out what to do !!
rota is now working

Once again thanks
your help is much appreciated

 

[judgehopkins]

I need to learn...tell us what you did!

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[jon92]

judge Hopkins

I can send you a copy if you let me know where
its 141k

jon92

 

[judgehopkins]

judgehopkinsREMOVETHIS@lycos.com

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.