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


    Thanks.
    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


    Code
    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

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


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


    SQL
    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 ?


    HTH


    Carl

Participate now!

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