Converting date to Week number

[Tom Tripicchio]

I am trying to total up the number of events in a given week over the
current year using the following formula in my query.

format([date], "yyyy,ww")

however this formula returns weeks 1-9 in a 1 digit format, therefore my
data then is not able to sort in week order. See example.

Week
# of events

2006,1
259

2006,10
286

2006,11
296

2006,12
306

2006,13
291

2006,14
272

2006,15
266

2006,16
237

2006,17
267

2006,18
249

2006,19
230

2006,2
263

2006,20
217



Is there a way to make the 1 digit weeks default to 2 digits when running
the query?

Thanks, Tom

 

[Guest]

format([date],"yyyy") & "," & format(format([date],"ww"),"00")

 

[Marshall Barton]

I am trying to total up the number of events in a given week over the
current year using the following formula in my query.

format([date], "yyyy,ww")

however this formula returns weeks 1-9 in a 1 digit format, therefore my
data then is not able to sort in week order. See example.

Week
# of events

2006,1
259

2006,10
286 [ . . .]

2006,2
263

2006,20
217



Is there a way to make the 1 digit weeks default to 2 digits when running
the query?

Try this

Year([date]) & Format(DatePart("ww", [date]), "00")