Avatar of MichelleLH
MichelleLH
 asked on

MS Access SQL query based off combobox AND textbox seach

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
Microsoft AccessSQL

Avatar of undefined
Last Comment
MichelleLH

8/22/2022 - Mon
mbizup

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] & "*")));
ASKER CERTIFIED SOLUTION
mbizup

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
MichelleLH

ASKER
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!
mbizup

Glad to help out :-)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Patrick Matthews

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
MichelleLH

ASKER
Thanks Patrick, that's a great tip!!