How do I get a line chart to plot the dynamic query I have created?

[humanexhaust]

I have created a form that allows the user to select some options that would turn into a line chart. I could set up line charts all day but as soon as I make some criteria that is dependant on a users choice it flips out. the data comes back correct but the chart will not output anything. I have searched the web for an answer and gave up. I am now currently trying to create a dynamic query that will then turn into a report. it works except for one thing, It will not work unless i encase one line in apostrophes ('). however, when i do, the chart won't accept the data. damned if i do and damned if i don't. the code is below:

Private Sub Command47_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("Dynamic_Query")
    
    strSQL = "SELECT T_BD.Gender, T_BD.DateCollected, T_BD.WhiteBloodCellCount " & _
        "FROM T_BD " & _
        "WHERE T_BD.Gender='" & Me.Gender_.Value & "' " & _
        "AND T_BD.DateCollected= 'Between #" & Me.Text9.Value & "# And #" & Me.Text11.Value & "#';"  'This line is the line that won't work without an apostrophe (')
        
    qdf.SQL = strSQL


    DoCmd.OpenReport "Dynamic_Report", acViewPreview
    
    Set qdf = Nothing
    Set db = Nothing
    
    'Debug.Print strSQL
    'MsgBox strSQL
End Sub

Above you can see that I am trying to gather a gender whether it is Male, Female, or Both and a range of dates. that is all. It should not be this hard to do.
This is what the report should look like:

This is what it ends up looking like:

And it's all because of the little apostrophe right here:

I have tried to delete the apostrophes but than the SQL statement throws an error.


Does anyone know of a good solution for the above issue or at least for me to generate a line chart with some simple user input?

[June7]

That is very odd error.

I never use parameterized query objects. I use VBA to open filtered report.

I do have graphs with a filter parameter in the RowSource SQL statement, mostly because haven't figured out graph Master/Child Links properties. Example:

SELECT A, W FROM GraphBMD WHERE LabNum=tbxLabNum;

The report is opened to specific LabNum and the graph data is dependent on the LabNum in the report textbox.


I deal with graph issues best when I can work with data. If you want to provide db for analysis, follow instructions at bottom of my post.

[humanexhaust]

June7,

Thanks for your reply. I will attach the DB as requested. Please note that this is a testing database that i am using to play around with which i will use to create the final product once I have hashed out all the kinks.

Once you open the DB you will have to press F11 to see "All Access Objects" (I like to hide these from the user).

From here you can close the Menu Tab because it is irrelevant for this issue

open the "Test_Option" Form.
This is the form that I have been creating all of my functions. it is broke down into sections and you should only be concerned with the section that has the button that says "Dynamic Query" at the very top.

Once the options have been selected and the button is clicked it will use some VBA and SQL code to create a query based on the users input on a template query called "Dynamic_Query"
This gets overwritten every time the button is clicked.

after the query has been updated it will automatically open a report called "Dynamic_Report" this should show a line chart with the white blood cell count over a specified period of time separated by gender.

I have determined that in the SQL code it will throw an error on the line:

strSQL = "SELECT T_BD.Gender, T_BD.DateCollected, T_BD.WhiteBloodCellCount " & _
        "FROM T_BD " & _
        "WHERE T_BD.Gender='" & Me.Gender_.Value & "' " & _
        "AND T_BD.DateCollected= 'Between #" & Me.Text9.Value & "# And #" & Me.Text11.Value & "#';"

Because it requires the apostrophes. when this gets sent to the query it also sends over the apostrophes. this causes an error because it becomes syntactically wrong. I can't win and i can't figure out a way around it.
Test BD.zip
The above is my compressed database. Thanks for whatever help you can offer.

[June7]

Incorporating a process that regularly modifies db design is usually a bad idea. Modifying query definition is modifying design.

If the only thing that is dynamic is the WHERE clause, this really should be used to open a filtered report, not modify query definition.

However, your graph RowSource is a CROSSTAB query. CROSSTAB queries handle filter parameters different from other queries so guess I can see why you are trying the approach to modify query definition. Review http://allenbrowne.com/ser-67.html#Param

None of my graphs use CROSSTAB query as data source. Consider this RowSource for graph with static criteria:

SELECT DateCollected, WhiteBloodCellCount FROM T_BD WHERE Gender="Male" AND DateCollected BETWEEN #1/1/2013# AND #12/31/2013#;

With dynamic criteria that references textboxes in report header section:

SELECT DateCollected, WhiteBloodCellCount FROM T_BD WHERE Gender=[tbxGender] AND DateCollected BETWEEN [tbxStart] AND [tbxEnd];

Could include the other blood count fields and they will show as more lines on the graph.

Now if you want male and female data on same graph, that does get a bit more complicated.

[humanexhaust]

I have tried to create a query that handles the users input which works but still does not allow the line chart to work correctly because of this line: Between [Forms]![Test_Option]![Text9] And [Forms]![Test_Option]![Text11]
The query is on the same database (that i included in my last post) named "Q_TestLineChart"

The query would filter the data correctly and it would show the results but once I tried to create a line chart from that query it would not work which is why i turned to VBA. If the normal functions don't allow you to complete your task you must build the functions.

I tried your static criteria in the RowSource and it worked perfectly but than I cant have static criteria because I have no idea what the user will want to do so i played around with your dynamic criteria in the same fashion and added a combo box (which turned into a text box) on the report with no luck and couldn't reference the choice. where have i gone wrong?

The entire concept of this database is for the user to input data and print reports with a line chart. the only variables that I need to be dynamic are the dates and the gender. I can make different reports for the types of blood samples. heck, i can even tripple the reports and make the gender non-dynamic, but there is no way i can know what date range the user wants.

[June7]

The filter input controls would have to be on a form to allow dynamic selection of parameters. The graph RowSource could reference form controls. This does require the form remain open when the report opens.

In my db, the filter controls are used to filter a bound report. Then the graph references a bound textbox on the report.

[humanexhaust]

June7,


Thank you for taking the time out to help me and you have gotten me closer to my goal than I was able to get on my own, but I have hit another wall after implementing your solution.

I have been able to get your suggestions to work but it is still not outputting the correct information.
What I need is for the data to output by gender. I want to be able to show either Male, Female, or Both. I will make separate charts for the different blood samples.

Your solution outputs this data:


I need to chart genders for the blood cell count. I also cant get them to list "Male" or Female" because it only wants to put "Gender". I have been playing around with it but dont know enough about it to know the right way to get the results i need.

Below is what the chart should look like:


both of these charts show the same data but the second one is more descriptive and shows the gender.

[June7]

Then I suppose to show graphing by genders will need the CROSSTAB query and set it up with filter parameters as demonstrated in Allen Browne link. Something I've never tried. I have done everything possible to avoid using CROSSTAB query at all in any of my databases, even for graphing.

The alternatives to CROSSTAB could be more complicated. Clearly, the approach to change query object definition is not viable, so options I see:

1. it is possible to emulate a CROSSTAB with expressions - then the filter criteria is simple

2. VBA to construct the graph RowSource sql and pass it to the report in OpenArgs and set the RowSource property - never tried this, I will try to do some testing

3. VBA procedure and a 'temp' table

EDIT: Just looked at raw data - each date does not have data for both genders. Displaying both genders causes gaps in the data and graph line is not continuous. Can plot the data points but connecting them with line not possible. How realistic is the table data? Shouldn't there be multiple male and female for each date?

[humanexhaust]

June7,

I have been able to play around with the methods you provided by just making the dates dynamic. I ended up making 16 reports instead of the 2 that I wanted, but without your assistance I would not have been able to figure it out with the CROSSTAB method that I was trying.

Time was essential with the development of this database because I have a client that thinks Access is the best thing since sliced bread and he is the only client that I have to create databases in Access with. He was also hassling me wanting it to be complete as soon as possible. I was able to finish it with your help and deliver it earlier today.

You have been very helpful and I appreciate your help with this project. I am going to do more research on a better way to do this in the future because I am sure I will run into this issue again.