Wrongly shown week numbers in tables

[Guest]

Hi there,

Some of our users are experience some issues in Access 2000 after new year.

The week numbers are calculate 1 week ahead in time, thus week 1 has become
week 2 etc...

In Outlook you can change this by choosing Tools -> Options -> Calendar
options and then click first-4-day-week.

How do you change this in Access ?

 

[Douglas J. Steele]

How are you calculating week? The two methods I'm aware of are using Format
and using DatePart. Both of them have optional parameters to allow you to
specify both firstdayofweek and firstweekofyear.

firstweekofyear is one of the following:

 

[Douglas J. Steele]

Oops: hit enter too soon!

Valid values for firstweekofyear are one of the following:

vbUseSystem (0): Use NLS API setting.
vbFirstJan1 (1): Start with week in which January 1 occurs (default).
vbFirstFourDays (2): Start with the first week that has at least four days
in the year.
vbFirstFullWeek (3): Start with the first full week of the year.

 

[Guest]

I have the same problem with those week numbers.
And if I understand you right, this is for the VB-functions. However, what
if I want to change the calculation in PivotTables (when date-fields are
grouped by week number automatically)?

 

[Douglas J. Steele]

Try creating a query that uses the appropriate function, then base your
PivotTable on that query, not on the table.

 

[Guest]

Yeah, of course thats a solution - my PivotTables and -Charts are already
based on Queries. However, in my case I have some 150 PivotTables and again
about 150 PivotCharts. All having the same problem. So there has to be an
easier solution! Any (other) ideas?

 

[Douglas J. Steele]

I seldom use pivot tables, so I had to look. I don't even get an option of
grouping by Week in either Access 97 or 2003, but if I choose one of the
other grouping options (Year, Quarter or Month), it uses Format in the
query. Take a look at the SQL associated with your PivotTables.

 

[Andi Mayer]

Yeah, of course thats a solution - my PivotTables and -Charts are already
based on Queries. However, in my case I have some 150 PivotTables and again
about 150 PivotCharts. All having the same problem. So there has to be an
easier solution! Any (other) ideas?

what you think about this:

Dim q As DAO.QueryDef
For Each q In CurrentDb.QueryDefs
If InStr(1, q.SQL, Chr(34) & "www" & Chr(34), vbTextCompare) Then
q.SQL = Replace(q.SQL, "www", "'www',vbMonday,vbFirstFullWeek")
End If
Next q
Set q = Nothing

don't forget to check it first in a copy!!!

 

[Guest]

i begin to love this thing here - i'm new in this forum and i have the
feeling we're getting close. thank you for your help so far.

i think you meant that i used the original date-field with the grouping
function. however it is different for me. when i open the field list in the
pivottable view for the query, i see an additional field <fieldname by week>
and i used this one to group by week. there is no corresponding field in the
query for this thing and i don't know how to change the calculation.

by the way: i also tried it with the original field and the grouping
function - the query didn't change, too. what am i doing wrong?

 

[Guest]

thank you for this one, but as i just wrote above, i do not see any changes
in the sql-statement of the query when changing things in the pivot table.

 

[Andi Mayer]

thank you for this one, but as i just wrote above, i do not see any changes
in the sql-statement of the query when changing things in the pivot table.

TRANSFORM Count(tmp_S_Orig3.ART_NR) AS AnzahlvonART_NR
SELECT tmp_S_Orig3.Her_Art_Nr, Count(tmp_S_Orig3.ART_NR) AS
[Gesamtsumme von ART_NR]
FROM tmp_S_Orig3
GROUP BY tmp_S_Orig3.Her_Art_Nr
PIVOT "Week " & Format([DATUM_AB],"www");

the interesting part is is the PIVOT in the query

but you are talking about a table?