Report Chart Y-Axis Assistance 1

[Mike555]

Hi. I have a bar chart on a report which displays data from the below query. As you can see this query has 3 columns: Station, OutToDate, WeekOf.

The chart displays Stations and their representative WeekOf as columns across the X-Axis. OutToDate is represented on the Y-Axis.

Is it possible to move the WeekOf field to the Y-Axis, so that the Y-Axis increments weekly, just as the data does in the query? When I open the Y-Axis properties, I can't seem to find where I can assign this field to the Y-Axis, as Access seems to want to format the chart automatically for me.

SELECT qryProductionDaysOut.Station, qryProductionDaysOut.OutToDate, IIf(Weekday(qryProductionDaysOut.OutToDate)=1,Format(qryProductionDaysOut.OutToDate, "Short Date"),
IIF(Weekday(qryProductionDaysOut.OutToDate)=2,Format(DateAdd("d", -1, [qryProductionDaysOut.OutToDate]), "Short Date"),
IIF(Weekday(qryProductionDaysOut.OutToDate)=3,Format(DateAdd("d", -2, [qryProductionDaysOut.OutToDate]), "Short Date"),
IIF(Weekday(qryProductionDaysOut.OutToDate)=4,Format(DateAdd("d", -3, [qryProductionDaysOut.OutToDate]), "Short Date"),
IIF(Weekday(qryProductionDaysOut.OutToDate)=5,Format(DateAdd("d", -4, [qryProductionDaysOut.OutToDate]), "Short Date"),
IIF(Weekday(qryProductionDaysOut.OutToDate)=6,Format(DateAdd("d", -5, [qryProductionDaysOut.OutToDate]), "Short Date"),
IIF(Weekday(qryProductionDaysOut.OutToDate)=7,Format(DateAdd("d", -6, [qryProductionDaysOut.OutToDate]), "Short Date"),
))))))) AS WeekOf
FROM qryProductionDaysOut
GROUP BY qryProductionDaysOut.Station, qryProductionDaysOut.OutToDate, qryProductionDaysOut.Priority
ORDER BY qryProductionDaysOut.Priority;

Thanks for any assistance.


--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.

 

[dhookom]

First of all, replace the horrible nested IIf()s with:
WeekOf: DateAdd("d",-Weekday([SchedDate]),[schedDate])+1

I'm surprised your sql runs without the weekof expression in the group by.

Beyond that, you might need to change rows for columns or something similar in the chart properties.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Mike555]

Thanks for the recommendation for the updated code!


--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.