Display Query Criteria In Report With A Chart

  • I have a query with critera in the date field ( Between [Type the beginning date:] And [Type the ending date:] )

    I want to create a chart from the data in the query, and display the date range at the top of the report. I have had no success.

    I tried: Creating a report based on the qry. Then inserting a chart based on the same query. Then a text box w/ ( =[Type the beginning date:] & " to " & [Type the ending date:] ) at the top of the report.

    When I try to view the report it ends up asking me for the start and end date several times? Can I make a report using the chart wizard? But then how do I display the criteria?

    I hope all this makes since. Thanks for the help.

  • Re: Display Query Criteria In Report With A Chart

    I am able to make my different parameters appear in a report.
    My query Criteria: Like [Enter Department]
    In the report, I have a text box w/ =[Enter Department]

    However when I try it in a report that also has a chart in it. It will ask "Enter Department" several times. Any ideas?

    Does it have to do with the report being based on a query and the chart being based on the same query?

    thanks again.

  • Re: Display Query Criteria In Report With A Chart

    Yes I think the problem will be that the report and the chart are based on the parameter query therefore it will ask for the parameters twice - at least.

    One way round would be to use VBA to ask for the two dates and store them in public variables then use a pair of functions to pass these back to the source of the report and chart.

    To start with you would need to base the report and the chart on the table. Then if you create a new VBA module and set up a couple of variables and functions to pass the "parameters" around. eg.

    Then in the open event of the report you can add a couple of inputboxes eg

    Private Sub Report_Open(Cancel As Integer)
    StartDate = InputBox("Enter Start")
    EndDate = InputBox("Enter End")
    End Sub

    Finally you can adjust the record source of the report to something like

    SELECT thedate, A, B FROM ChartData WHERE thedate>=startdatef() And thedate<=enddatef();

    and add the same WHERE clause in to chart. For example

    SELECT (Format([TheDate],"DDDDD")),Sum([A]) AS [SumOfA],Sum([B]) AS [SumOfB] FROM [ChartData]  WHERE thedate>=startdatef() And thedate<=enddatef()   GROUP BY (Int([TheDate])),(Format([TheDate],"DDDDD"));

    Attached example

    In the end I think a VBA solution gives you more control but I guess it looks a bit complicated. Let's hope somebody comes up with something simple. Maybe base the report on the parameter query and pass the parameters to two text boxes in the report then base the chart on the table with a WHERE clause using the textbox values ?



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!