Full Version: Pivot Table - Date In Horizontal Axis
UtterAccess Forums > Microsoft® Access > Access Forms
phisaw
Hi,
I have a pivot chart on my form and have the horizontal axis grouped by weeks, but it gives a number for the week of the year. Is there anyway I can have it show the beginning day of the week as a label? For example, Monday as the beginning of the week, this weeks label would be 06/17/13.
Is this possible? I've searched many posts and if I did find one similar, it often wasn't answered.
If anyone has the answer, I would greatly appreciate it.
Thanks in advance,
Pam
RJD
Hi Pam: One way to do this is to resolve your dates back to Monday of their week prior to charting, instead of grouping by week. Then you can group on that date. Then the axis should show the Monday dates. To do this you can use this...
!--c1-->
CODE
[YourDate]-Weekday([YourDate],2)+1 As MondayDate

...replacing, of course, YourDate with the name of your date...
Odid not test this in a chart (I did test the date calc), but it should work okay.
HTH
Joe
phisaw
Hi Joe,
Thanks for replying. I tried your suggestion, but it still shows the # of the week.
Do you have any other suggestions?
Thanks again,
Pam
phisaw
Hi Joe,
spoke too soon. I took the "Group By Week" off and it does show the 1st day of the week as the horizontal axis label.
Thank you, thank you, thank you.
RJD
br />Glad you got that sorted out - and that it works as you want it to...
Regards,
Joe
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.