Week number from date

[blake7]

Hi all, I have the code below in a text box which is returning the week
number from text box 118 on a report, it is returning week 4 when the date in
the text box is 18th January 2010 when actually it should read week 3, can
anyone tell me what is wrong? Thanks

=DatePart("ww",[text118])

 

[John Spencer]

Use the additional parameters of DatePart to set what is to be used to
calculate when week 1 starts. For instance

DatePart("ww",#2010-01-18#,vbsunday,vbFirstFullWeek) returns 3
and
DatePart("ww",#2010-01-18#,vbsunday,vbFirstFourDays) returns 3
but
DatePart("ww",#2010-01-18#,vbsunday,vbFirstJan1) returns 4

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

DatePart has some arguments such as the first day of the week and first week
of the year. Below might work for you; however, you really need to check all
the options to see what meets your business rules.

DatePart("ww",[text118],,3)

 

[blake7]

Thanks Guys, I used Jerry's solution although I had to remove one of the
comma's at the end - eg DatePart("ww",[text118],3) and it works fine.
Thanks. Tony

DatePart has some arguments such as the first day of the week and first week
of the year. Below might work for you; however, you really need to check all
the options to see what meets your business rules.

DatePart("ww",[text118],,3)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi all, I have the code below in a text box which is returning the week
number from text box 118 on a report, it is returning week 4 when the date in
the text box is 18th January 2010 when actually it should read week 3, can
anyone tell me what is wrong? Thanks

=DatePart("ww",[text118])