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
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