Query gets values from 3 combo boxes but does not give correct results

[juniormint2009]

Hello all, I am new here, pretty new to access also but have programmed before. I have a form with 3 combo boxes, each combo box has its own table to fill it with values (tblStaff has field staff with about 5 people) (tblSeverity has Severity field with 4 number severities 1 2 3 4) and (tblStatus has field status with 4 status's Repaired, Working, Broke)
When a user picks values from the combo box, then click "search" (a button on my form that's onclick Event Procedure is "DoCmd.OpenQuery "comp_search", acViewNormal, acEdit" in VB. Now, comp_search is a simple query with the main table "master" in it, 1st field just shows master.* for all the fields, then I have 3 fields for the combo box references, the field for the 1st is staff from the master table and the criteria is "[Forms]![comp_search]![cboAgents]", then Severity (again from the master table) with criteria "[Forms]![comp_search]![cboSeverity]", and 3rd field on query is status from the master table with criteria "[Forms]![comp_search]![cboStatus]". The cboAgents is the name of the 1st combobox that's rowsource is "table/query" and the table is tblAgents, same with the other 2 "cboSeverity" rowsource="table/query" table is tblSeverity, 3rd combo is cboStatus rowsource="Table/Query" table=tblStatus.
I also setup for null values with 3 other cells in the query set to ([master].[staff]) Like [Forms]![comp_search]![cboAgents] and Is Null in the criteria so they can choose not to pick a combobox, now if I query on one value like the staff member, I get all the records for that staff member, but if I try and add in 2 criteria's like a certain Staff member with a severity of something like 2 I get a result with all the records. If I choose just severity is 2 I get all my severity 2 result etc.. So why is it not filtering when I choose more than 1 combo box ? They are all unbound colum boxes that get there values from a simple table for each, like severity has 4 rows with each severity like 1 then 2nd record is 2 and so on till 4.
Sorry for the long post!

 

[dhookom]

Just post the SQL view and someone will probably suggest the correct syntax. I think you just need to set the criteria to something like:

Like [Forms]![comp_search]![cboAgents] or [Forms]![comp_search]![cboAgents] Is Null

Duane
Hook'D on Access
MS Access MVP

 

[juniormint2009]

Thanks dhookom, here it is when I clicked on design view

SELECT master.*
FROM master
WHERE (((master.[Opened By])=[Forms]![comp_search ]![cboAgents]) AND ((master.[Ticket Status])=[Forms]![comp_search ]![cboStatus]) AND ((master.Severity)=[Forms]![comp_search ]![cboSeverity])) OR (((([master].[Opened By]) Like [Forms]![comp_search ]![cboAgents]) Is Null) AND ((([master].[Ticket Status]) Like [Forms]![comp_search ]![cboStatus]) Is Null) AND ((([master].[Severity]) Like [Forms]![comp_search ]![cboSeverity]) Is Null)) OR (((master.Severity)=[Forms]![comp_search ]![cboSeverity])) OR (((master.[Opened By])=[Forms]![comp_search ]![cboAgents]) AND ((master.[Ticket Status])=[Forms]![comp_search ]![cboStatus])) OR (((master.[Ticket Status])=[Forms]![comp_search ]![cboStatus])) OR (((master.[Opened By])=[Forms]![comp_search ]![cboAgents])) OR (((master.[Opened By])=[Forms]![comp_search ]![cboAgents])) OR (((master.[Opened By])=[Forms]![comp_search ]![cboAgents]) AND ((master.[Ticket Status])=[Forms]![comp_search ]![cboStatus])) OR (((master.[Ticket Status])=[Forms]![comp_search ]![cboStatus])) OR (((master.[Opened By])=[Forms]![comp_search ]![cboAgents]));

 

[dhookom]

I'm not sure why you are mixing "Like" in there. How does this work for you

SELECT master.*
FROM master
WHERE ([Opened By]=[Forms]![comp_search ]![cboAgents] OR [Forms]![comp_search ]![cboAgents] Is Null) 
AND ([Ticket Status]=[Forms]![comp_search ]![cboStatus] OR [Forms]![comp_search ]![cboStatus] Is Null) 
AND (Severity=[Forms]![comp_search ]![cboSeverity] OR [Forms]![comp_search ]![cboSeverity] Is Null) ;

Duane
Hook'D on Access
MS Access MVP

 

[juniormint2009]

WOW, now the query works perfect, only problem is when I run the form, after I select the values from my drop down list, I have a "Search" button that does this on the "OnClick" part

DoCmd.OpenQuery "comp_search", acViewNormal, acEdit

Now, when I click the button, the query asks me for the paramaters instead of just running and returning results based on the combobox values ??
If I run the query alone, I get the parameter windows and can fill them in and get the correct results but I want it to get them from the combo boxes from my form "comp_search"
Any way to correct this ?
Thanks for the help!!! So far its getting there.

 

[dhookom]

Some how your post of the SQL got a bunch of extra spaces at the end of control names. I'm not sure how your SQL got [comp_search[COLOR=yellow red] [/color]] rather than [comp_search].

Duane
Hook'D on Access
MS Access MVP

 

[juniormint2009]

YOU ARE PROGRAMMING GODS!!! IT WORKSS!! Finally! Thank you all!, took those spaces out and bamm!
You guys are great, I got a nice looking app going and that part was killing me! I can't thank you guys enough! You are the best!