Searching database not working properly
Hi guys, Im searching a database with the following code
Set rs = db.OpenRecordset("SELECT * FROM Leads WHERE `agent` = " & "'" & Form1.Text1.Text & "' AND " & _
"UCase( [Company Name]) LIKE '*" & UCase(Text1.Text) & "*' OR " & _
"UCase( [Contact Name]) LIKE '*" & UCase(Text1.Text) & "*' OR " & _
"UCase( [Phone Number]) LIKE '*" & UCase(Text1.Text) & "*' OR " & _
"UCase( [Contact Number2]) LIKE '*" & UCase(Text1.Text) & "*' OR " & _
"[Mobile] LIKE '*" & Text1.Text & "*' ")
now it displays the results in my listview correctly, how ever the part that does not work is WHERE `agent` = " & "'" & Form1.Text1.Text & "' AND
it's showing all the data that is in the database that contains what i type in text1.text
how ever i need it to only display the ones that contain text1.text where agent = form1.text1.text
any ideas?
regards
Jamie
Re: Searching database not working properly
You need to add some bracketing
Code:
Select * from table Where Field1=Value And (Field2=Value Or Field3=Value Or Field4 = value)
As coded in the OP either the first 2 have to match OR any one of the others
with the brakets the first one has to match AND any one of the others