Help with multiple values for search

[joenewbie]

Good day! I need help with regards to entering values for search filter. I have a database to pull up using customer number and year. Lets say, I type in 101 for cust.no. and 2015 for year it works fine but how do I go about it that the entry will accept '2013-2015' or '2013,2014,2015' or 2014;2015. Hope someone can help me. here's the code.

Sub GetCustOrderCopy()    
    Dim lngCustNo As Long
    Dim lngYear As Long
    Dim iRow As Long
    Dim iCol As Long
    
    Dim strTemp As String
    Dim strFilter As String


    Call mCleanSheet
    
    If compOrder Is Nothing Then
        MsgBox ("You need to login first!")
        Exit Sub
    End If
    
      
    iRow = 11
    iCol = 1
    
    'checks if input number is numeric
    If IsNumeric(Cells(5, 2)) Then
        lngCustNo = CLng(Cells(5, 2))
    Else
        MsgBox "Enter CustomerNo in cell 5,2"
        Exit Sub
    End If
    If lngCustNo = 0 Then
        MsgBox "Enter CustomerNo in cell 5,2"
        Exit Sub
    End If
    
    
    If IsNumeric(Cells(6, 2)) Then
        lngYear = CLng(Cells(6, 2))
    Else
        MsgBox "Enter Year in cell 6,2"
        Exit Sub
    End If
    If lngYear = 0 Then
        MsgBox "Enter Year in cell 5,2"
        Exit Sub
    End If
    
    
    ' Get the filter for CustomerNo in the BC_CustomerOrderCopy Component
        'strFilter = ""
        strTemp = compOrder.bcGetTableObjectName(COP_CustomerNo)
        strFilter = strTemp & " = " & lngCustNo
        strFilter = strFilter & " AND "


        
    ' Get the filter for DeliveryYearNo in the BC_CustomerOrderCopy Component
        strTemp = compOrder.bcGetTableObjectName(COP_DeliveryYearNo)
        strFilter = strTemp & " = " & lngYear
        
        
        lretval = compOrder.bcSetFilterRequeryStr(strFilter)
        lretval = compOrder.bcFetchFirst(0)


    While lretval = 0
    
        Cells(iRow, iCol) = compOrder.bcGetStr(COP_DeliveryCustomerName)
        Cells(iRow, iCol + 1) = compOrder.bcGetStr(COP_DeliveryAddress1)
        Cells(iRow, iCol + 2) = compOrder.bcGetInt(COP_InvoiceNo)
        Cells(iRow, iCol + 3) = compOrder.bcGetDouble(COP_TotalGross)
        Cells(iRow, iCol + 4) = compOrder.bcGetDouble(COP_TotalVAT)
        Cells(iRow, iCol + 5) = compOrder.bcGetDouble(COP_TotalAmount)
        iRow = iRow + 1
        lretval = compOrder.bcFetchNext(0)
        
    Wend
End Sub

[Kenneth Hobs]

Welcome to the forum!

I guess it depends on how you are doing the filter. The best approach would likely be to set a helper column I suspect. Setup a formula in it to be True or False for the partial match.