Chart Query

[waseem0888]

I have concatenated the data from 2 tables through the union query below to get my dates on Combobox for my modern charts.

SELECT Format([Date_Report],"mmmm yyyy") AS Month
FROM tblNcr_Sor
UNION
SELECT format([Date_Report],"mmmm yyyy")AS Month
FROM tblWir_Itp;

But when i use this query on my combo row source the data i will get in the combo but for the charts, it still shows me the error to Enter Parameter value?

Link child fields : Month
Link Master field ; cmbMonth (My combo)

When i use query form one of my any table to this combo its working fine but from union query its not. Any one who can explain where i am worng

Thank you

 

[theDBguy]

Hi. Is that the Row Source of your Combobox or the SQL statement of a saved query?

 

[waseem0888]

Hello. I create a separate new union query through the above SQL statement and then i use this query to my combo box rowsource .

 

[theDBguy]

Hello. I create a separate new union query through the above SQL statement and then i use this query to my combo box rowsource . View attachment 97755

Okay, you are saying when you double click that query from the nav pane, it opens without any prompt, right? But, when you select it as the row source for your combo, you get the prompt, right?

 

[waseem0888]

Yes its opening form nav pane. but when i try to link it to my charts through combo then its prompts me and if i click cancel on this prompt i can see the data in the combobox but charts are giving me this prompt below is the property of chart.

Rowsource of combobox from this query

 

[theDBguy]

Not sure I'm following yet. Are you able to post a sample db? For one thing, using "Month" as an alias could be problematic, because it's also the name of a built in function.

 

[waseem0888]

Alright. I have tried changing to Month_Year but it gives the same error. I am attaching DB as a sample.

 

[theDBguy]

Alright. I have tried changing to Month_Year but it gives the same error. I am attaching DB as a sample.

Hi. Thanks. Does your attachment include the chart? If so, I'm afraid it's not available to my version of Access. Otherwise, I didn't get any error when I opened the form and clicked on the combobox or selected an item from it.

 

[waseem0888]

Yes, it's having a chart when i am opening its giving me an error also i am using office 365. see below screenshot

 

[Minty]

You need to add Month_Year as a named parameter to the transformed Chart query.
The query is effectively a crosstab, and you have to declare the parameters in a crosstab query.

Click on the design and then right click and select Parameters.

Edit: Having had a further look you are specifying Month_Year as the link child field, but I don't see that anywhere in the query or transformed data?

 

[waseem0888]

Hi Minty Thank you for the suggestions i earlier found this on the internet i tried creating a crosstab query also and link to my combo box i was facing the same issue i am getting my data on combo but on charts it giving me the same error. Or do you mean to create a crosstab query and used it on chart rowsource? because for chart rowsource i have another query I need only for combo union query to get dates in the combo from two different tables. Could you please give it a try you may resolve this issue?

Edit: I have used Month_Year from the union query to my combo resource. and link to chart
Link child fields : Month_Year
Link Master field ; cmbMonth (My combo)

 

[arnelgp]

see changes in qrySor and the AfterUpdate event of the combobox on the form.
also remove the parent/child link fields.

 

[waseem0888]

Thanks arnelpg for your solution. you handle the problem in a nice way.