1st week of month as Week 1

[som_35711]

I have a query which displays the date as week (image attached)
But is there any way the 1st week of every month shows as week 1.


SQL of query is below.

TRANSFORM Sum(Table1.[Total]) AS SumOfTotal
SELECT Table1.[User], Sum(Table1.[Total]) AS Total
FROM Table1
GROUP BY Table1.[User]
PIVOT Format([Date],'"Week" ww of mmm yyyy');

[June7]

Did you try single w?

[som_35711]

Single w gives weird results

[June7]

Sorry, my memory failed. Single "w" is day of week number. Turns out there is no intrinsic function for monthly week number. Requires custom function.

Here is an example:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1085