Extracting the week number from a date

[JohnL]

I would like to do in Access what I know how to do in Excel. My table has a
field formatted as Date/Time. In a query I would like to create a new column
using the column which contains the date info to determine, for example, what
the week number is.
In Excel, if cell A2 contains 6/9/08, I can use in cell B2, =Text(A2,"YYYY")
and get 2008 or use =WeekNum(A2) and get 24.
Any and all suggestions welcomed.

TIA

John

 

[bhicks11 via AccessMonster.com]

Hi John,


=Format(Now(), "ww") Displays the number of the week of the year the current
date represents, where ww is 1 through 53

Replace NOW() with the field name.

Bonnie

http://www.dataplus-svc.com

 

[JohnL]

Bonnie,

Thanks for the tip. Once I realized brackets ([]) were needed around the
field name, it worked like a charm! Format([Postg Date],"ww").

And then I experimented: it works great for the month (“mm†gives me 06,
“mmm†Jun, “mmmm†June), for the day (“dd†09, “ddd†Mon, “dddd†Monday) and
the year also ( "yy" 08, “yyy†08161 ((the 161st day of the year)) "yyyy"
2008, yyyyy 2008161). And even “yyyyww†200824, the 24th week of 2008!

Thanks again Bonnie, I couldn't have done it without you.

John

 

[bhicks11 via AccessMonster.com]

Glad to be of help!

Bonnie

http://www.dataplus-svc.com

Bonnie,

Thanks for the tip. Once I realized brackets ([]) were needed around the
field name, it worked like a charm! Format([Postg Date],"ww").

And then I experimented: it works great for the month (“mm†gives me 06,
“mmm†Jun, “mmmm†June), for the day (“dd†09, “ddd†Mon, “dddd†Monday) and
the year also ( "yy" 08, “yyy†08161 ((the 161st day of the year)) "yyyy"
2008, yyyyy 2008161). And even “yyyyww†200824, the 24th week of 2008!

Thanks again Bonnie, I couldn't have done it without you.

John

Hi John,

[quoted text clipped - 18 lines]