Link to home
Start Free TrialLog in
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
Avatar of mbizup
mbizup
Flag of Kazakhstan image

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
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MichelleLH
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!
Glad to help out :-)
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
Thanks Patrick, that's a great tip!!