Searching database not working properly

[JamieWarren09]

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

[DataMiser]

You need to add some bracketing

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