PDA

View Full Version : Help with multiple values for search



joenewbie
06-14-2015, 04:09 PM
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
06-15-2015, 07:07 AM
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.