add WHERE clause to query with vba (queryDef)

Misiek

Registered User.
Local time
Today, 05:22
Joined
Sep 10, 2014
Messages
248
Hello,

I have a TRANSFORMED query:

Code:
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.
Code:
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.
 
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?
 
In this statement:

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

by "field" they really mean the combo.
 
Works like a charm! So simple and so clever.
Thank you very much :)
 
Happy to help!
 

Users who are viewing this thread

Back
Top Bottom