add WHERE clause to query with vba (queryDef)

[Misiek]

Hello,

I have a TRANSFORMED query:

TRANSFORM nz(count(T_qa.qaQAPK),0) AS SumOfQAs
SELECT month(qaDate) AS QAmonth, Count(T_qa.qaQAPK) AS QAs
FROM Q_ALL_qa
GROUP BY Month(qaDate)
PIVOT month([qaDate])
IN (1,2,3,4,5,6,7,8,9,10,11,12);

This query is record source for a report, then this report show all calculations in a form.
I have 7 of each (query + report) all showing on the same form.

All those queries calculate data for all departments.

In the form, I have placed a combobox.

What I want is to create a vba code which will add clause WHERE to all queries at the same time and then run it.

WHERE qaDeptFK=Forms!F_CompLvl.cboDeptStats

However, if nothing has been selected in the combo, I want the queries to calculate data as normal, for all departments.

Where do I place the vba statement?
is it under combobox AfterUpdate event?

I am planning to use this: (As I never done it before)
http://www.upsizing.co.uk/Art26_RecordSets.aspx
Modifying SQL On-The-Fly section

Thank you for your advice.

 

[pbaldy]

Would this work instead?

http://access.mvps.org/access/queries/qry0001.htm

 

[Misiek]

In theory it should do the magic
But the field in the table this department field will always have a value

And I understand if I select is null the query will look for records where department fields is blank,but I want it to look for ALL, or am I wrong?

 

[pbaldy]

In this statement:

"if the field is left blank, all records are returned by the query"

by "field" they really mean the combo.

 

[Misiek]

Works like a charm! So simple and so clever.
Thank you very much

 

[pbaldy]

Happy to help!