Dynamically set up database search on multiple fields

[Kye]

It has been a while since using vb an now I have started a project and I am not sure where my mistake is in my code.

Basically what I am doing is trying to build a multiple search string with certain criteria being asked, the criteria is in 4 separate fields with a search where I am able to enter multiple information, or just search a single field . Now it works to a certain extent but when trying to enter a double field for model and colour it pulls back all the makes of different vehicles at the same time which don't match the criteria that is requested


My code goes as this

[CODE=vb]Private Sub CmdFind_Exit()

Dim rs As Object
Dim VEHILCE_MAKE As String
Dim VEHICLE_MODEL As String
Dim VEHILCE_COLOUR As String
Dim LICENSE_PLATE As String
Dim strsql As String

Me.VEHICLE_MAKE .SetFocus

VEHICLE_MAKE = Me.VEHICLE_MAKE .Text

Me.VEHICLE_MODE L.SetFocus

VEHICLE_MODEL = Me.VEHICLE_MODE L.Text

Me.VEHICLE_COLO UR.SetFocus

VEHICLE_COLOUR = Me.VEHICLE_COLO UR.Text

Me.LICENSE_PLAT E.SetFocus

LICENSE_PLATE = Me.LICENSE_PLAT E.Text

If VEHICLE_MAKE <> "" Or VEHICLE_MODEL <> "" Or VEHICLE_COLOUR <> "" Or LICENSE_PLATE <> "" Then
DoCmd.SetWarnin gs False

strsql = "select * from Sheet1 where [VEHICLE MAKE] = '" &VEHICLE_MAK E & "'or [VEHICLE MODEL]='" & VEHICLE_MODEL & "' or [VEHICLE COLOUR]='" & VEHICLE_COLOUR & "' or [LICENSE_PLATE]='" & LICENSE_PLATE & "'"

Set rs = CurrentDb.OpenR ecordset(strsql )


Me.Reference_su bform.Form.Reco rdSource = strsql

Me.Reference_su bform.Form.Requ ery
Me.Form.Refresh
End If

End Sub[/CODE]

[Killer42]

Actually, if you think about it, looking for records matching a couple of fields with an OR clause like that, will often return everything.

Let's say I ask for Make = 'FORD" OR Colour = 'Yellow'. The database is going to return all of the FORDs, and all of the yellow cars. This is clearly not what you want.

Although it's far from ideal, I think the simplest way to make this work might be to change all the OR to AND, and default all the entries to "*".