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] & "*"));