Write SQL in VBA for rowsource of chart

[nederbragt]

I want to make the rowsource of a chart on a form variable.
I want to choose for wich product the chart must drawn.

I do have the next SQL statement.

TRANSFORM Count(qryCrediteringenVT.Product) AS
CountOfProduct
SELECT (Format([Datum],"mmm"" '""yy&quot) AS Expr1
FROM qryCrediteringenVT
WHERE (((qryCrediteringenVT.Product)="Test"))
GROUP BY (Year([Datum])*12+Month([Datum])-1), (Format
([Datum],"mmm"" '""yy&quot)
PIVOT qryCrediteringenVT.Product;

Can anyone help me to write this SQL statement in VBA, so I can make the criteria of the product variable ?

Any suggestions welcome, otherwise I have to make a new query for every new product.

 

[nashman]

One method is to create a combo box on your form for your WHERE clause. Call it txt_WHERECLAUSE. Then create a command button which will change the rowsource for your chart. You can reference your txt_WHERECLAUSE in your SQL statement like this...

[Forms]![Your form name]![Your chart object name].rowsource=TRANSFORM Count(qryCrediteringenVT.Product) AS
CountOfProduct
SELECT (Format([Datum],"mmm"" '""yy&quot) AS Expr1
FROM qryCrediteringenVT
WHERE (((qryCrediteringenVT.Product)= " & txt_WHERECLAUSE & &quot)
GROUP BY (Year([Datum])*12+Month([Datum])-1), (Format
([Datum],"mmm"" '""yy&quot)
PIVOT qryCrediteringenVT.Product;
DoCmd.RepaintObject acForm,"Your form name"
DoEvents

This should refresh your chart and update it with your newly selected criteria.

 

[nederbragt]

Thank you for the response.

I only have one more problem.
There are to many quotes in de code.

The SQL statement I have written is the one from a query.
I need to transform the code in VBA language.

 

[GummowN]

replace the " in your format statements with '

This should then work - he says confidently

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[JeremyNYC]

...a little too confidently.

What you want to do is enclose the text parameter in single quotes, just like you would if you were writing the sql directly in the query window. It's just two small changes to one line:

WHERE (((qryCrediteringenVT.Product)= '" & txt_WHERECLAUSE & "'))

Note the single quotes.

Also, you'll have to concatenate all of that, or else put it all on one, long, ugly line in your code.

You can do that like this:
[Forms]![Your form name]![Your chart object name].rowsource= "TRANSFORM Count "_
& " (qryCrediteringenVT.Product) AS " _
& " CountOfProduct " _

etc.

Oh, now I see what the last poster was saying. The quotes in all of the format commands _will_ have to be changed to single quotes.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.