calendar weeks - nls api settings?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Back
Top