SQL multi-table query and charts

[maGemme]

Hi everyone,

I'm having problems with a chart I'm creating.

I have 2 queries, 1 SQL multi-table query and another that runs from the multi-table query results.

The second has form filters based on drop boxes, the allow the user to chose what they want to see in the chart.

Both queries run great by themselves but when I use the filtered one in the chart, I get the following error :

The Microsoft Jet database engine does not recognize '[Forms]![frmOCCReport]![cboCenter]' as a valid field name or expression

When I run the chart without the filters it works great.

Here is the code for both of my queries.
Multi-table:

SELECT CSS, Agent_Name, Product, Account_Number, Notes,  Date, "Questions"
as [Work type] FROM tblQuestions
UNION SELECT CSS, Agent_Name, Product, Account_Number, Notes, Date,  "Referrals"
as [Work type] FROM tblRequests
UNION SELECT CSS, Agent_Name, Product, Account_Number, Notes, Date,  "Escalations"
as [Work type] FROM tblEscalation
UNION SELECT CSS, Agent_Name, Product,  Account_Number, Notes, Date,  "Settlements"
as [Work type] FROM tblSettlement
UNION SELECT CSS, Agent_Name, Product,  Account_Number, Notes, Date,  "CREW/CLD"
as [Work type] FROM tblCREW;

2nd query based of the multi-table:

SELECT qryMultiTable.CSS, qryMultiTable.Agent_Name, qryMultiTable.Product, qryMultiTable.Account_Number, qryMultiTable.Notes, qryMultiTable.Date, qryMultiTable.[Work type]
FROM tblStaff_list INNER JOIN qryMultiTable ON tblStaff_list.[CDSE NAME] = qryMultiTable.Agent_Name
WHERE (((Year([Date]))=Year(Now())) AND (([Centre]=[Forms]![frmOCCReport]![cboCenter] Or [Forms]![frmOCCReport]![cboCenter] Is Null)=True) AND (([Current Manager]=[Forms]![frmOCCReport]![cboManager] Or [Forms]![frmOCCReport]![cboManager] Is Null)=True));

I hope my question is clear enough, thank you in advance!

 

[pbaldy]

See if this helps:

http://support.microsoft.com/kb/209778

 

[maGemme]

See if this helps:

http://support.microsoft.com/kb/209778

That's excatly what I needed!!! Thank you thank you thank you!!!

 

[pbaldy]

Happy to help!