Passing instructions to combo box filter...

[andy_dyer]

Hiya,

This may be really easy... but I can't do it...

I have a form with a subform embedded - the subform is based on a query that reads the combo box cboOwner and only displays the records that match the entry...

My problem is there are three status of owner - "Operations", "Technology" and "Joint"...

I then have three filters I wish to apply - "Operations", "Technology" and "All"

For Operations Filter all projects with both "Operations" and "Joint" as the owner...

For Technology Filter all projects with both "Technology" and "Joint" as the owner...

For All Filter all projects...

My query thatt runs the subform and looks up the combo is:

SELECT tblProject.ID, tblProject.[Project Name], tblProject.[Project Owner (Project)] AS [Project Owner], tblProject.[% Complete], tblProject.[Amount Invoiced], tblProject.[Project Value (Project)] AS [Project Value], tblProject.[Operations Value (Project)] AS [Operations Value], tblProject.[Technology Value (Project)] AS [Technology Value], tblProject.[Project Status], tblProject.[Client Name], tblProject.[Project Name] AS [P name1]
FROM tblProject
WHERE (((tblProject.[Project Owner (Project)])=[forms]![frmQuery]![cboOwner]) AND ((tblProject.[Project Status])="Open") AND ((tblProject.[Client Name])<>"SEAMS"))
ORDER BY tblProject.[Client Name], tblProject.[Project Name];

But at present I get literally only Operations if I select Operations, Technology if I select Technology and nothing if I select All...

Apologies if this should go in the query part of the forum...

 

[DCrake]

I Presume you are setting the recordsource using the after update of the combo box. In that case your sql should read

StrSql = "SELECT ID, [Project Name], [Project Owner (Project)] AS [Project Owner], [% Complete], [Amount Invoiced], [Project Value (Project)] AS [Project Value], [Operations Value (Project)] AS [Operations Value], [Technology Value (Project)] AS [Technology Value], [Project Status], [Client Name], [Project Name] AS [P name1]
FROM tblProject
WHERE ((([Project Owner (Project)])=" & Me.CboOwner & "')
AND (([Project Status])='Open')
AND (([Client Name])<>'SEAMS'))
ORDER BY [Client Name], [Project Name];"

Notice how the combo box is concetenated into the string and encompassed with quotes, plus the other hard coded strings in single quotes.

 

[andy_dyer]

Thanks for coming back to me...

When I paste the sql code into my query I get an error:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'

Any ideas what could be causing that?

 

[DCrake]

If you do a Debug.Print StrSql what is being returned?

You can actually ccopy this into a new query sql and Access will tell you where the error lies in the string.

David

 

[andy_dyer]

Thanks for your help...

I've only just discovered Union Queries and realised that I could use one for this!