Control chart date grouping using VBA?

[Smoothcat74]

Hi everyone,

I have a report that contains a chart built off of a saved query. The x-axis of this chart is a list of dates. What I would like to do is give the user an option based on a set of radio buttons as to how they would like to group the dates: daily, weekly or monthly.

How would I use VBA to update the grouping of the query/chart prior to loading the report?

Thanks for any help!

 

[genomon]

Capture the date ranges and use them in a BETWEEN clause in the source query. You will probably need to copy the SQL, modify it, and assign it to the Chart's RowSource property
before the chart opens. Are you calling the report from a form?

Cogito eggo sum – I think, therefore I am a waffle.

 

[dhookom]

I'm not sure you need any code. Assuming a form named "frmRptCrit" with an option group named "opgDateInterval" with options like:
[tt]
1 Daily
2 Weekly
3 Monthly
4 Quarterly
[/tt]

You can create a derived column in a query with an expression like:

XAxis: DatePart(Choose([Forms]![frmRptCrit]![opgDateInterval],"y","ww","M","Q"),[OrderDate])

Duane
Hook'D on Access
MS Access MVP

 

[genomon]

I always do it the hard way. Nice catch, Duane!

Cogito eggo sum – I think, therefore I am a waffle.