wrong "work week" for group by

[Steve]

Last year (2008) it calculated the work week corectly, this year (2009) it is
off due to the way we use the first week with at least 4 days as WW 1. I
think this should be easy to fix but not found an easy way to tell the form
that calculates for a chart to correct for this. Any tips on the solution?

 

[Dirk Goldgar]

Last year (2008) it calculated the work week corectly, this year (2009) it
is
off due to the way we use the first week with at least 4 days as WW 1. I
think this should be easy to fix but not found an easy way to tell the
form
that calculates for a chart to correct for this. Any tips on the
solution?

You don't show the code or expression you're using, so it's hard to say.
The DatePart function has an optional argument, <firsrtweekofyear> which can
be used to specify what week to consider the first week. One of the
possible values you may specify for this argument is 2 (vbFirstFourDays),
which tells the function to start with the first week that has at least four
days in the new year. Is that what you're using? If not, perhaps it
should be.

 

[Steve]

Not really using any code just the built in feature from the chart using the
form wizard.
From the field list I see my Datetime field named Timestamp and there is
also a Timestamp: by Week. I am just dragging that to the X axis. I have
looked into what you describe below but see no way to make that work. Is
there some way to include that in the way I am using this form?

 

[Dirk Goldgar]

Not really using any code just the built in feature from the chart using
the
form wizard.
From the field list I see my Datetime field named Timestamp and there is
also a Timestamp: by Week. I am just dragging that to the X axis. I have
looked into what you describe below but see no way to make that work. Is
there some way to include that in the way I am using this form?

I don't do much charting, but try this:

1. Open the form in design view

2. Click on the chart object and open its property sheet

3. Go to the Data tab of the property sheet

4. Click on the Row Source line. A "build" button (caption "...") will
appear at the end of the line.

5. Click on the build button. The rowsource query will open in the query
designer.

6. One of the calculated fields shown in the query grid will (I think)
contain a DatePart() expression along the lines of:

DatePart("ww",[Timestamp],0)

Change that expression to:

DatePart("ww",[Timestamp],0, 2)

Note that there will probably be other elements in the calculated field's
expression; just change this part.

7. Another of the calculated fields may contain a Format() expression along
these lines:

Format([Timestamp],"WW 'YY")

Change that expression to:

Format([Timestamp],"WW 'YY", 0, 2)

Again, only change this part of the calculated field's expression.

If I'm right, that ought to take care of it. However, these instructions
could be wrong in some way, if I've guessed wrong about how you have this
set up.

 

[Steve]

I couldn't figure out how to do exactly what you mention below. But I did go
to the query which I used for the form, added a column called WW and used the
DatePart formula as you described.
That worked and I can use it to properly show the WW on the chart.

It is a shame there seems to be no way to control this as an option in the
group by feature.

Not really using any code just the built in feature from the chart using
the
form wizard.
From the field list I see my Datetime field named Timestamp and there is
also a Timestamp: by Week. I am just dragging that to the X axis. I have
looked into what you describe below but see no way to make that work. Is
there some way to include that in the way I am using this form?

I don't do much charting, but try this:

1. Open the form in design view

2. Click on the chart object and open its property sheet

3. Go to the Data tab of the property sheet

4. Click on the Row Source line. A "build" button (caption "...") will
appear at the end of the line.

5. Click on the build button. The rowsource query will open in the query
designer.

6. One of the calculated fields shown in the query grid will (I think)
contain a DatePart() expression along the lines of:

DatePart("ww",[Timestamp],0)

Change that expression to:

DatePart("ww",[Timestamp],0, 2)

Note that there will probably be other elements in the calculated field's
expression; just change this part.

7. Another of the calculated fields may contain a Format() expression along
these lines:

Format([Timestamp],"WW 'YY")

Change that expression to:

Format([Timestamp],"WW 'YY", 0, 2)

Again, only change this part of the calculated field's expression.

If I'm right, that ought to take care of it. However, these instructions
could be wrong in some way, if I've guessed wrong about how you have this
set up.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I couldn't figure out how to do exactly what you mention below. But I did
go
to the query which I used for the form, added a column called WW and used
the
DatePart formula as you described.
That worked and I can use it to properly show the WW on the chart.

Well done!

It is a shame there seems to be no way to control this as an option in the
group by feature.

Possibly there could be some feature in the user interface to select the
first-week option, either in the Chart wizard or even for a whole database
(overriding the Access default). You may want to submit it to Microsoft as
a suggestion for future releases.