Private Sub cmdSearch_Click()
Dim strSearch As String
Dim strSQL As String
Dim FieldName As String
strSearch = Me.txtSearch.Value
Select Case Me.grpWhere.Value
Case 1 'Customer Number
FieldName = "[Cust]"
Select Case Me.grpHow.Value
Case 1 'Starts With
strSearch = GetBegin(FieldName, strSearch)
Case 2 'Ends With
strSearch = GetEnd(FieldName, strSearch)
Case 3 'Contains
strSearch = getBoth(FieldName, strSearch)
End Select
Case 2 'Customer Name
FieldName = "[Name]"
Select Case Me.grpHow.Value
Case 1 'Starts With
strSearch = GetBegin(FieldName, strSearch)
Case 2 'Ends With
strSearch = GetEnd(FieldName, strSearch)
Case 3 'Contains
strSearch = getBoth(FieldName, strSearch)
End Select
Case 3 'Both
Select Case Me.grpHow.Value
Case 1 'Starts With
strSearch = GetBegin("[Cust]", strSearch) & " OR " & GetBegin("[Name]", strSearch)
Case 2 'Ends With
strSearch = GetEnd("[Cust]", strSearch) & " OR " & GetEnd("[Name]", strSearch)
Case 3 'Contains
strSearch = getBoth("[Cust]", strSearch) & " OR " & getBoth("[Name]", strSearch)
End Select
End Select
strSQL = "SELECT Cust, [Name], Address, City FROM qry_ChainDetail WHERE & strSearch ORDER BY [Cust]"
With Me.lstSearch
.RowSource = strSQL
.Requery
Debug.Print strSQL
End With
End Sub
Public Function GetEnd(FieldName, strSearch As String) As String
GetEnd = FieldName & " like '" & strSearch & "#'"
End Function
Public Function GetBegin(FieldName, strSearch As String) As String
GetBegin = FieldName & " like '*" & strSearch & "'"
End Function
Public Function getBoth(FieldName, strSearch As String) As String
getBoth = FieldName & " like '*" & strSearch & "#'"
End Function