MS Access SQL query based off combobox AND textbox seach

MichelleLH
MichelleLH used Ask the Experts™
on
Hi there,

New to this forum and pretty much self taught using MS Access. I've googled my way out of many an issue, but this one is making me want to give up (and I hate giving up!!)

I have a search form for users. On this form is a combobox, a text box, and a listbox.

Step 1, users filter search using the combobox (which displays Campaigns). The Listbox is requeried and now displays all contacts who are part of the selected campaign. (works fine). At this point if the user sees the contact they want to open, they double-click the contact in the listbox and an edit form pop-ups (works fine, yay!).

Step 2, users filters the campaign contacts displayed in listbox further by a textbox search, which I want to search multiple fields (firstname, lastname, emailaddress1 etc.). (listbox is requeried to display contacts from the selected campaign, whose firstname is like "alan" or lastname is like "alan" etc.)

My problem is: I can only get the above to work with the selected campaign and ONE text box filter added. When I try to add another field/column for the textbox to filter by, my query spits the dummy, and completely ignores the campaign selected in the combobox.

Here is my sql query with the campaign combobox specified and, firstname field or lastname field (which only works when I have ONE textbox field searched on) - I need to add many other fields for my textbox to search on. Can anyone help please?!

SELECT Enquiry.ID, Enquiry.Campaign, Contact.FirstName, Contact.LastName
FROM Contact RIGHT JOIN Enquiry ON Contact.ID = Enquiry.Enquirer
WHERE (((Enquiry.Campaign)=[forms]![Search Enquiry Details]![Cbocampaign]) AND ((Contact.FirstName) Like "*" & [forms]![Search Enquiry Details]![txtbxsearcheq] & "*")) OR (((Contact.LastName) Like "*" & [forms]![Search Enquiry Details]![txtbxsearcheq] & "*"));

Kind Regards,
Michelle
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Try grouping your ORs using parentheses:


SELECT Enquiry.ID, Enquiry.Campaign, Contact.FirstName, Contact.LastName
FROM Contact RIGHT JOIN Enquiry ON Contact.ID = Enquiry.Enquirer
WHERE (((Enquiry.Campaign)=[forms]![Search Enquiry Details]![Cbocampaign]) AND (((Contact.FirstName) Like "*" & [forms]![Search Enquiry Details]![txtbxsearcheq] & "*")) OR (((Contact.LastName) Like "*" & [forms]![Search Enquiry Details]![txtbxsearcheq] & "*")));
Nerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
You can also simplify this a great deal by removing the automatic parentheses and table prefixes that Access adds.  Those table prefixes are only necessary when the same fieldname appears in multiple tables:

SELECT Enquiry.ID, Campaign, FirstName,  LastName
FROM Contact RIGHT JOIN Enquiry ON Contact.ID = Enquiry.Enquirer
WHERE Campaign =[forms]![Search Enquiry Details]![Cbocampaign] AND (FirstName Like "*" & [forms]![Search Enquiry Details]![txtbxsearcheq] & "*"  OR LastName Like "*" & [forms]![Search Enquiry Details]![txtbxsearcheq] & "*")

Open in new window


The only parentheses that are needed are those around the ORs, to set a precedence of operations.

Author

Commented:
OMG!!!! I love you :) I feel like an idiot, and this works perfectly! And such a fast response!

Sorry re formating - I actually built the query using design view of Access, but figured it was easier to paste the SQL view.

Thank you Mbizup x10000!
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Glad to help out :-)
Top Expert 2010

Commented:
No points for this, please :)

mbizup's query is of course correct, and there is nothing wrong with it.

As a matter of preference, whenever my queries have >1 table involved, I do like to indicate explicitly which table each column comes from, but with a twist: I use table aliases.

Thus, mbizup's code

SELECT Enquiry.ID, Campaign, FirstName,  LastName
FROM Contact RIGHT JOIN Enquiry ON Contact.ID = Enquiry.Enquirer
WHERE Campaign =[forms]![Search Enquiry Details]![Cbocampaign] AND (FirstName Like "*" & [forms]![Search Enquiry Details]![txtbxsearcheq] & "*"  OR LastName Like "*" & [forms]![Search Enquiry Details]![txtbxsearcheq] & "*")

Open in new window


can become:

SELECT e.ID, e.Campaign, c.FirstName, c.LastName
FROM Contact c RIGHT JOIN 
    Enquiry e ON c.ID = e.Enquirer
WHERE e.Campaign =[forms]![Search Enquiry Details]![Cbocampaign] AND 
    (c.FirstName Like "*" & [forms]![Search Enquiry Details]![txtbxsearcheq] & "*"  OR 
    c.LastName Like "*" & [forms]![Search Enquiry Details]![txtbxsearcheq] & "*")

Open in new window


The table aliases, in this case 'c' and 'e', allow me to use a shorthand to refer back to the tables.  That way I can be specific about qualifying the column reference, but without having to re-type all the table names.

:)

Patrick

Author

Commented:
Thanks Patrick, that's a great tip!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial