Multiple field search

[clarke24]

Hi,
I currently have some code that allows me to search for an field in a form, but what i would like to do is change it so that i can search for multiple fields to return a specific result. Can anyone give me any pointers please.


Private Sub Command22_Click()

Dim WOlist As String

If Me.Select = 1 Then
sel = "WorkorderNo ='" & Me.Input & "'"
ElseIf Me.Select = 2 Then
sel = "[Work Type].WorkTypeDescription ='" & Me.Input1 & "*'"
ElseIf Me.Select = 3 Then
sel = "WorkStatus.Workstatus ='" & Me.Input2 & "'"
ElseIf Me.Select = 4 Then
sel = "ProblemDescription like '" & Me.Input3 & "*'"
ElseIf Me.Select = 5 Then
sel = "DateReceived like '" & Me.Input4 & "*'"
ElseIf Me.Select = 6 Then
sel = "AssetNo like '" & Me.Input5 & "*'"

End If

WOlist = "SELECT Workorders.WorkorderNo, [Work Type].WorkTypeDescription, WorkStatus.WorkStatus, Workorders.ProblemDescription, Workorders.DateReceived, Workorders.PMTarCompDate, Workorders.AssetNo " & _
"FROM (Workorders LEFT JOIN [Work Type] ON Workorders.WorkType = [Work Type].WorkTypeID) LEFT JOIN WorkStatus ON Workorders.WorkStatus = WorkStatus.WorkStatusID " & _
"WHERE " & sel & " ;"

Me.List.RowSource = WOlist
Me.List.ColumnCount = 7
Me.List.ColumnHeads = True
Me.List.ColumnWidths = "3 cm; 3 cm; 3 cm; 7 cm; 3 cm; 3 cm; 3 cm "


End Sub

 

[PHV]

A starting point:

sel = ""
If Trim(Me.Input & "") <> "" Then
    sel = sel & " AND WorkorderNo='" & Me.Input & "'"
End If
If Trim(Me.Input1 & "") <> "" Then
    sel = sel & " AND [Work Type].WorkTypeDescription LIKE '" & Me.Input1 & "*'"
End If
...
If Trim(Me.Input5 & "") <> "" Then
    sel = sel & " AND AssetNo LIKE '" & Me.Input5 & "*'"
End If
sel = Mid(sel, 6)
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

Just to add to PHV's code:

[green]
'WHERE 1 + 1 = 2 AND ...[/green][blue]
sel = " 1 + 1 + 2 "[/blue]
If Trim(Me.Input & "") <> "" Then
    sel = sel & " AND WorkorderNo='" & Me.Input & "'"
End If
....

Have fun.

---- Andy

 

[clarke24]

Thanks Guys got it working now. Thanks a lot for your help.