troubleshooting Question

MS Access SQL query based off combobox AND textbox seach

Avatar of MichelleLH
MichelleLH asked on
Microsoft AccessSQL
6 Comments1 Solution779 ViewsLast Modified:
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,
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros