i have a very simple and though complex problem:

i have a table with a date field ([date_field]), and some value
([value_field]). now ia want to create a pivot-chart with calendar weeks on
the x-axis going from e.g. cw 50/04 to cw 5/05.

now to my problem:

if i choose in the field list "data_field by week" and drag the entry year
and week to the x-axis access is calculating the cw-number wrongly (first cw
is week with 01.01.05)

if i am using the format function (int(format([date_field];"ww";2;2))) i get
the correct numbers for the calendar week. dragging this field to the chart
results in incorect sorting (cw 01/05, 02/05, 53/04). so i added the year out
of the "data_field by week" to the x-axis and got: 2004/53, 2005/01, 2005/02
but then also 2005/53 for the 01.01.05 and 02.01.05 which is incorrect of
course.

how can i show calendar weeks sorted correctely with a range moving over 2
years?

thx for your help - as always it's somehow urgent!

I haven't tried this going from one year to the next, but similar sorting
issues can be fixed by converting the Format function back to a date, i. e.:

CDate((Format([date_field];"ww";2;2)))

-Ed Kopta

"Klaus Aschauer" <(E-Mail Removed)> wrote in message
newsE24B864-0E82-443C-A566-(E-Mail Removed)...
>i have a very simple and though complex problem:
>
> i have a table with a date field ([date_field]), and some value
> ([value_field]). now ia want to create a pivot-chart with calendar weeks
> on
> the x-axis going from e.g. cw 50/04 to cw 5/05.
>
> now to my problem:
>
> if i choose in the field list "data_field by week" and drag the entry year
> and week to the x-axis access is calculating the cw-number wrongly (first
> cw
> is week with 01.01.05)
>
> if i am using the format function (int(format([date_field];"ww";2;2))) i
> get
> the correct numbers for the calendar week. dragging this field to the
> chart
> results in incorect sorting (cw 01/05, 02/05, 53/04). so i added the year
> out
> of the "data_field by week" to the x-axis and got: 2004/53, 2005/01,
> 2005/02
> but then also 2005/53 for the 01.01.05 and 02.01.05 which is incorrect of
> course.
>
> how can i show calendar weeks sorted correctely with a range moving over 2
> years?
>
> thx for your help - as always it's somehow urgent!