Week-Quarter date format

[jmersing]

I'm trying to display all 52 weeks of the year in the following format "Qtr1 Week1". Here is what I'm using now:

PIVOT "Q" & Format([Week],"q") &" " & "Week" &format([Week], "ww") ;

But what I need is to have the weeks start at Week1 every quarter. Right now it returns:

Q1 Week12 - Q1 Week13 - Q2 Week14 - Q2 Week15

I'm trying to display:

Q1 Week12 - Q1 Week13 - Q2 Week1 - Q2 Week2 etc.

Any Ideas?
Thanks
JIm

 

[Mile-O]

"Week" & (Format([Week], "ww") MOD 4)

 

[jmersing]

Thanks, but that displayed the following:

Week0 , Week1, Week2, Week3

?

I was hoping for Q1 Week1, Q1 Week2
Q2 Week1 Q2 Week2 etc.

 

[Mile-O]

Sorry, forgot the +1 at the end:

"Week" & ((Format([Week], "ww") MOD 4) + 1)

 

[jmersing]

Thanks again but maybe I am not explaining myself clear enough. I want the column names to literaly reflect the Quarter of the year and the 13 weeks that included in that quarter.

So if I ran a report for all of 2003 the column names would be:

Q1 Week1 Q1 Week2 Q1 Week3 Q1 Week4 Q1 Week5 Q1 Week6 Q1 Week7 Q1 Week8 Q1 Week9 Q1 Week10 Q1 Week11 Q1 Week12 Q1 Week13 Q2 Week1 Q2 Week2 Q2 Week3 Q2 Week4 Q2 Week5 Q2 Week6 Q2 Week7 Q2 Week8 Q2 Week9 Q2 Week10 Q2 Week11 Q2 Week12 Q2 Week13 Q3 Week1 Q3 Week2 Q3 Week3 Q3 Week4 Q3 Week5 Q3 Week6 Q3 Week7 Q3 Week8 Q3 Week9 Q3 Week10 Q3 Week11 Q3 Week12 Q3 Week13 Q4 Week1 Q4 Week2 Q4 Week3 Q4 Week4 Q4 Week5 Q4 Week6 Q4 Week7 Q4 Week8 Q4 Week9 Q4 Week10 Q4 Week11 Q4 Week12 Q4 Week13

 

[namliam]

"Q" & format([Week],"q",vbMonday,vbFirstFourDays) & " Week " & Format([Week],"WW",vbMonday,vbFirstFourDays) - (format([Week],"Q",vbMonday,vbFirstFourDays)-1)*13

like so?

 

[jmersing]

Thanks
I'm not coding this at the present time though, I'm just using a regular query which will not accept vbMonday etc. Are you implying that this will require coding to complete? Because that is fine with me.

Jim

 

[Mile-O]

Sorry, I'm being thick today.

Anyway, with with nam's given, you can replace the vb constans like so:

vbSunday = 1
vbMonday = 2
...
vbSaturday = 7

 

[jmersing]

So you are saying that I have to code the SQL statement, not use a regular query, correct?

My original question was how to do this in a query not code. The query does not recognize vbMonday, vbFirstDay etc.

 

[namliam]

If you want to use this in more than just this query its usually smart to do it in a function.... its easier all around...

As mile said allready, replace the vbMonday by 2
and the vbFirstFourDays by 2 also...

Making it:
"Q" & format([Week],"q",2,2) & " Week " & Format([Week],"WW",2,2) - (format([Week],"Q",2,2)-1)*13

Or the function:
Function MakeString(Week as date) as string
makestring = "Q" & format(Week,"q",vbMonday,vbFirstFourDays) & " Week " & Format(Week,"WW",vbMonday,vbFirstFourDays) - (format(Week,"Q",vbMonday,vbFirstFourDays)-1)*13
end function

Then in the query all you need is:
Expr: Makestring([Week])

Greetz

 

[jmersing]

OK that makes sense, so I should PIvot on Makestring([Week])? In that format on my crosstabquery.

How does the query know to look to VBA to get the new field name?

My db is a form that allows the user to filter the datasheet in an attched subform (frmcross2). there is also a different subform attched called (frmviewlist) That form just has a straightforward Select Query. There are combos on top of the main form that filter the results of the subforms using a STRSQL statement. They must select an option button to activate (make visible) each subform.

Just wanted to give you that background. I'm going to try the new code now, and Thanks Again!
Jim

 

[namliam]

OK that makes sense, so I should PIvot on Makestring([Week])? In that format on my crosstabquery.

Yes

How does the query know to look to VBA to get the new field name?

Because your using the homemade function....

Just wanted to give you that background. I'm going to try the new code now, and Thanks Again!
Jim

Good luck

 

[jmersing]

It worked great! That is exactly what I was trying to do. But I have one question, why does it work?

what are the references to ,2,2 and the -1 part? I've never seen that before

Thanks again!!

Heres is the SQL for others to see:

"Q" & format([Week],"q",2,2) & " Week " & Format([Week],"WW",2,2) - (format([Week],"Q",2,2)-1)*13

 

[namliam]

The 2's are allready posted in one of my previous posts...

The -1 is simply -1

The Qaurter = 2
Week (of the year) = 25

Your result Q2 Week 12 is the result of 25 - 13 (beeing the 13 of the first quarter)

Q = 3
Week = 36 => Your result Q3 Week 10 (beeing 36 - (2*13), 2 quarters of 13 weeks)
ergo: Weeknr - (Qaurter -1)*13

Note: Its NOT waterproof, Every now and again you have a year of 53 weeks, thus 1 quarter of 14 weeks. This is NOT supported, you will have to create a function for that one. this is the "easy" fix.

Greetz