Wrongly shown week numbers in tables

P

Per Nielsen

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 ?
 
D

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:
 
D

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.
 
K

Klaus Aschauer

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)?
 
D

Douglas J. Steele

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

Klaus Aschauer

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? ;)
 
D

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.
 
A

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? ;)
Click to expand...
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!!!
 
K

Klaus Aschauer

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