Straighforward date Question

[poulsotw]

Hello

I have the following expressions in 3 date fields - Date, Week No. and Period, in a form :-

Dat, Default Value =Date()

Week No., Control Source =DateDiff("w","1-1",[Dat])

Period No., Control Source =DateDiff("w","1-1",[Dat])/4


for the days of this week the following values have been returned in the form fields,

Tues 27th Period 5, wk20
Weds 28th Period5.25, wk21
Thur 29th Period 5.25, wk21
Fri 30th Period 5.25, wk21
Sun 01, Period 5.25, wk 21

The 2 issues are that the wk changes on a weds and the wk no. is 1 behind reality.

Presumably the week flips after a tues because the year 2003 started on a weds. The business year for my company started on mon 6th Jan which is really wk 2 of the year. I am also guessing that the wk is 1 behind as Access is counting completed weeks so for the first wk it is 0 rather than 1 ?

Is there a means of correcting the change in week no. to happen on another day ? Also is it acceptable practice to just put a +1 in the wk calculation or is the a mor proper means of doing this ?

any suggestions appreciated,

cheers,
Toby

 

[Rich]

Use the following two functions


Public Function FinancialYear(dDate As Date) As Integer
FinancialYear = Year(dDate) - IIf(dDate < DateSerial(Year(dDate), 1, 6), 1, 0)
End Function


Public Function FinancialWeek(dDate As Date) As Integer
FinancialWeek = (Date - DateSerial(FinancialYear(Date), 1, 6)) \ 7
End Function
To use in a calculated control =FinancialYear([Dat]) and =FinancialWeek([Dat])

 

[poulsotw]

1st Week still defaults to week 0 ...

Rich

thanks for this I have put the 2 functions in - unfortnately the FinancialWeek returns a value of 0 for the first week from 6th Jan to Sun 12th. Do you have any suggestions how to set this to 1 ?
Does this mean it will **** the last week of teh year as week 51 ?

cheers,
Toby.

 

[Rich]

Try =FinancialWeek([Dat])+1, I should check your spelling on the last post, the work count doesn't read correctly