Sorting Dates into Week numbers 1

[DropsIT]

Does anyone know if there is a function that will allocate dates into week nuumbers by either financial year or calender year within a query ?

Thanks

 

[JonoB]

I thought that the datepart function may help with this, but I cant remember offhand.

If not, another solution would be to create a calculation that counts that number of days that have elapsed in the current year, and divide that number by 52, and then make an integer of that number.

Something like
=int((date()- dateserial(year(date()-1),12,31)/52)

Hope this helps

 

[JonoB]

ooops - sorry - you should be dividing by 7, not 52 in the above example.....and change to number into a positive.

=Int((Date()-DateSerial(Year(Date()-1),12,31))/-7)

 

[JonoB]

Bad, its obviously a monday morning!!!

=Int(([ctldate]-DateSerial(Year(Date())-1,12,31))/7)

 

[PaulBricker]

JonoB is right about the DatePart Function
DatePart("ww",[DateField])

"ww" returns the week number


Paul

 

[sedgely]

Have just come across this thread and it part solves my problem. however...this solution seems to calculate based on first day of week being Sunday, i need to start my weeks on Monday, how can this be achieved?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[BattleDroid42]

From the Access help file on DatePart:

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

The firstdayofweek argument has these settings:

Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday

The firstweekofyear argument has these settings:

Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in the new year.
vbFirstFullWeek 3 Start with first full week of the year.