Limit chart to date range based on a form's textboxes

[Art Marks]

I'm currently opening a report where the date range is limited based on the
values of 2 textboxes on a form using code like this:

Private Sub cmdOpenrptWeight_Click()

DoCmd.OpenReport "rptWeight", acPreview, , "[Date]>=#" & TxtMinDate & "#
and [Date]<=#" & TxtMaxDate & "#"

End Sub

Problem is that the charts at the end of the report aren't affected. I'm
using an SQL SELECT statement for the RowSource of the charts.

Any solutions? Can I design the charts with the RowSource property set and
then modify that property in the sub shown above to include a WHERE clause
with the [Date] limited?

Thanks
--Art

 

[Arvin Meyer [MVP]]

Yes, you can set the rowsource property of the report to a query which reads
the dates from the same form. Use the build button (right-click in the
criteria box) Manually, you can just add the form references to the criteria
box, like:

Between [Forms]![YourFormName]![txtMinDate] And
[Forms]![YourFormName]![txtMaxDate]
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[John Vinson]

I'm currently opening a report where the date range is limited based on the
values of 2 textboxes on a form using code like this:

Private Sub cmdOpenrptWeight_Click()

DoCmd.OpenReport "rptWeight", acPreview, , "[Date]>=#" & TxtMinDate & "#
and [Date]<=#" & TxtMaxDate & "#"

End Sub

Problem is that the charts at the end of the report aren't affected. I'm
using an SQL SELECT statement for the RowSource of the charts.

Any solutions? Can I design the charts with the RowSource property set and
then modify that property in the sub shown above to include a WHERE clause
with the [Date] limited?

If you use SQL explicitly referencing the form it should work:

strSQL = "... WHERE [Date] BETWEEN [Forms]![YourForm]![txtMinDate] " _
& "AND [Forms]![YourForm]![txtMaxDate]"


John W. Vinson[MVP]

 

[Guest]

Hi Art,

I'm using an SQL SELECT statement for the RowSource of the charts.

Can you base the charts on saved queries? You should be able to add a
criteria to a saved query that looks to your form to pick up it's parameters.
It should look something like this in query design view:

Field: Date
Table: Your table name

The following is all on one line:
Criteria: Between [Forms]![YourFormName]![TxtMinDate] And
[Forms]![YourFormName]![TxtMaxDate]

where "YourFormName" is the name of the form that includes TxtMinDate and
TxtMaxDate.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I'm currently opening a report where the date range is limited based on the
values of 2 textboxes on a form using code like this:

Private Sub cmdOpenrptWeight_Click()

DoCmd.OpenReport "rptWeight", acPreview, , "[Date]>=#" & TxtMinDate & "#
and [Date]<=#" & TxtMaxDate & "#"

End Sub

Problem is that the charts at the end of the report aren't affected. I'm
using an SQL SELECT statement for the RowSource of the charts.

Any solutions? Can I design the charts with the RowSource property set and
then modify that property in the sub shown above to include a WHERE clause
with the [Date] limited?

Thanks
--Art