Year + weeknr from date-field

[corine]

In a table I have a date-field called "Date"
Now I must make a crosstab-query with a field yearnumber-weeknumber and group by on that field. The weeknumber must alwyas be 2 digits long because I have to sort on this field (descending).
Hopefully someone can help me

Thanks
Corine, Amsterdam

 

[MichaelRed]

MySortField: Format([Date], "yyyyww&quot

As in ? Format(Now, "yyyyww&quot
200117

This needs to be in a Select Query which becomes the RecordSource for the Crosstab.

Please note that in the general case, the crosstab WILLNOT generate a record unless the data field(s) are present. So, for example if there is no activity for this week, (200117) there will be no record for the week in the crosstab output. This is a common problem in the use of crosstab queries. Individuals EXPECT to see a record for each "Group", and often insist on generating a dummy record as a place holder for reports ...




MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[corine]

Problem with your solution is that if the weeknumber is 9 or less, the sorting won't work, because the field will be

20001 (jan 2000) instead of 200001
Watch the number of zeros, 3 / 4

So, unfortunately your solution won't work as far as I can see.
Thanks for warning for the missing values!

Corine

 

[MichaelRed]

MyDate = #01/01/01#
? Format(MyDate, "yyyy&quot & Right("00" & Trim(Str(Format(MyDate, "ww&quot)), 2)
200101


So Sorry. I keep getting lost on which arg forces Charc and which don't.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[AArt]

Our mainframe uses integers for dates, and I use this for today's date:
Val(CStr(Year(Now()))+(IIf(Len(CStr(Month(Now())))=1,"0"+CStr(Month(Now())),Str(Month(Now())))))
It's ugly, but itsa mainframe. Most parens I've ever used in one day.

 

[MichaelRed]

AARt,

Yse UGLLLYYYYYYYYYYY!. Look at the various format options. PLEASE!


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[corine]

I did, but could not find it. If you can tell me the simple, NOT ugly way, PLEASE tell me!

Corine

 

[corine]

Aart,

You are working with MONTH, I need WEEKS.
Your solution works for monthes...... and that is possible with the format function, but now weeks

Corine

 

[MichaelRed]

Corine,

Look at MY last post (before this one). It does WORK (ONLY SEMI-uGLY!


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[corine]

Michael

THANKS!!! And the ugliness.... I can live with!!!

Corine