Per Week Format

[Guest]

I have a wizard generated chart in one of my databases. It displays the call
per week. The labels are formatted to display the week as week# Year. The
resulting display is 41 '05, 42 '05, etc. I would like it to display and the
first day of the week. Such as 11/20/05, 11/27/05, etc. I found the code
listed below in the properties of the chart but I can not figure out change
it to reflect the format I need or if it can be done. Any help would be
appreciated.... Thanks, Bill

TRANSFORM Count([Migrated]) AS [CountOfMigrated] SELECT (Format([MDate],"WW
'YY")) FROM [Migrated All] GROUP BY (Year([MDate])*CLng(54) +
DatePart("ww",[MDate],0)-1),(Format([MDate],"WW 'YY")) PIVOT [StaffType];

 

[OfficeDev18 via AccessMonster.com]

Have you tried changing Format([MDate],"WW 'YY")) to Format([MDate],
"MM/DD/YY WW 'YY")), or will that give you too many dates?

HTH

I have a wizard generated chart in one of my databases. It displays the call
per week. The labels are formatted to display the week as week# Year. The
resulting display is 41 '05, 42 '05, etc. I would like it to display and the
first day of the week. Such as 11/20/05, 11/27/05, etc. I found the code
listed below in the properties of the chart but I can not figure out change
it to reflect the format I need or if it can be done. Any help would be
appreciated.... Thanks, Bill

TRANSFORM Count([Migrated]) AS [CountOfMigrated] SELECT (Format([MDate],"WW
'YY")) FROM [Migrated All] GROUP BY (Year([MDate])*CLng(54) +
DatePart("ww",[MDate],0)-1),(Format([MDate],"WW 'YY")) PIVOT [StaffType];

 

[Guest]

Thanks, I gave that a try but it did not group it weekly any longer and as
you guessed it produced too many dates.....

-Bill

Have you tried changing Format([MDate],"WW 'YY")) to Format([MDate],
"MM/DD/YY WW 'YY")), or will that give you too many dates?

HTH

I have a wizard generated chart in one of my databases. It displays the call
per week. The labels are formatted to display the week as week# Year. The
resulting display is 41 '05, 42 '05, etc. I would like it to display and the
first day of the week. Such as 11/20/05, 11/27/05, etc. I found the code
listed below in the properties of the chart but I can not figure out change
it to reflect the format I need or if it can be done. Any help would be
appreciated.... Thanks, Bill

TRANSFORM Count([Migrated]) AS [CountOfMigrated] SELECT (Format([MDate],"WW
'YY")) FROM [Migrated All] GROUP BY (Year([MDate])*CLng(54) +
DatePart("ww",[MDate],0)-1),(Format([MDate],"WW 'YY")) PIVOT [StaffType];

 

[Duane Hookom]

Try something like:

TRANSFORM Count([Migrated]) AS [CountOfMigrated]
SELECT DateAdd("d", 1-Weekday([MDate]),[MDate])
FROM [Migrated All]
GROUP BY DateAdd("d", 1-Weekday([MDate]),[MDate])
PIVOT [StaffType];