'Created: 22-Jan-2007
'Version: 1.94.8
'Programmer: Joe Schwarz
'Purpose: Dynamically builds the SQL statement to query for the list of orders
' according to current search parameters. This replaces the original
' programmer's highly inefficient OR-everything-together query
' (qry_PointOfSales).
Private Sub RequeryList(Optional SpecificCriteria As String = "")
Dim SQL As String
Dim WHERE As String
Dim ORDER_BY As String
Dim Criteria As String
Dim Criteria_WHERE As String
Dim TempPhone As String
Dim TempInvoiceNo As String
Dim OrderStatus As Integer
Dim dteTempDate As Date
On Error GoTo ErrHandler
Call HourglassMouse
SQL = "SELECT DISTINCTROW [Invoice #], Customer, Phone, " & _
"[Ship To], Format(OrderedDate,""d/m/yy"") AS Ordered, Format(ArrivingDate,""d/m/yy"") AS Arriving, Closed, IsEstimate AS [Is Estimate] " & _
"FROM qry_POS_List"
'Always will filter out soft-deleted orders
WHERE = " WHERE IsDeleted=False"
'Filter by order status open/closed?
OrderStatus = Nz(cboSHow, STATUS_ALL)
If OrderStatus = STATUS_OPEN Then
WHERE = WHERE & " AND Closed = 'No'"
ElseIf OrderStatus = STATUS_CLOSED Then
WHERE = WHERE & " AND Closed = 'Yes'"
End If
'Filter by CommissionID?
If Nz(cboCommissionTo, 0) <> 0 Then
WHERE = WHERE & " AND CommissionEmployeeID=" & cboCommissionTo
End If
'Filter for Orders or Estimates only?
If Nz(cboType, 2) <> 2 Then
If cboType = 0 Then
WHERE = WHERE & " AND IsEstimate = 'No'"
Else
WHERE = WHERE & " AND IsEstimate = 'Yes'"
End If
End If
'Filter by date range?
If Nz(txtDateFrom, "") <> "" Or Nz(txtDateTo, "") <> "" Then
If Nz(txtDateFrom, "") = "" Then txtDateFrom = txtDateTo
If Nz(txtDateTo, "") = "" Then txtDateTo = txtDateFrom
'Automatically fix unlogical date range
If txtDateTo < txtDateFrom Then
dteTempDate = txtDateTo
txtDateTo = txtDateFrom
txtDateFrom = dteTempDate
End If
WHERE = WHERE & " AND (OrderedDate Between #" & Format(txtDateFrom, "medium date") & _
"# AND #" & Format(txtDateTo, "medium date") & "#)"
End If
'Check to search against any of Invoice #, Phone #, or Customer Name
If Len(SpecificCriteria) > 0 Then
Criteria = SpecificCriteria
Else
Criteria = Trim(Nz(txtName, ""))
End If
If Criteria <> "" Then
'Check against Company or Ship To fields
Criteria_WHERE = "(Customer Like ""*" & Criteria & "*"")"
Criteria_WHERE = Criteria_WHERE & " OR " & _
"([Ship To] Like ""*" & Criteria & "*"")"
'Check to see if this could be a phone number
TempPhone = Criteria
TempPhone = Replace(TempPhone, "-", "")
TempPhone = Replace(TempPhone, " ", "")
TempPhone = Replace(TempPhone, "(", "")
TempPhone = Replace(TempPhone, ")", "")
If IsNumeric(TempPhone) Then
If Len(TempPhone) >= 7 Then
Criteria_WHERE = Criteria_WHERE & " OR (IIf(Not IsNull([phone])," & _
"Replace(Replace(Replace(Replace([Phone],""-"",""""),"" "",""""),"")"",""""),""("","""") " & _
"Like ""*" & TempPhone & "*""))"
End If
End If
'Check if there's a possibility of an invoice number (use the telephone variable which
'has already stripped out the dashes, spaces, and paranthesis)
TempInvoiceNo = Val(TempPhone)
If TempInvoiceNo > 0 Then
Criteria_WHERE = Criteria_WHERE & " OR ([Invoice #] Like ""*" & _
TempInvoiceNo & "*"")"
End If
End If
'Join the text-based search criteria to the other search criteria (it is surrounded in
'parenthesis because there may be up to 3 fields being searched against that are OR'd
'together)
If Len(Criteria_WHERE) > 0 Then
WHERE = WHERE & " AND (" & Criteria_WHERE & ")"
End If
'Join the SELECT statement with the WHERE statement
SQL = SQL & WHERE
ORDER_BY = " ORDER BY Customer, [Invoice #] DESC"
SQL = SQL & ORDER_BY
lstOrders.RowSource = SQL
lstOrders.Requery
Exit_Routine:
Call DefaultMouse
Exit Sub
ErrHandler:
MsgBox "Error occurred while trying to build search filter, error # " & _
Err.Number & ": " & vbCrLf & Err.Description, vbExclamation, "Error in RequeryList"
GoTo Exit_Routine
End Sub
![[sadeyes]](/data/assets/smilies/sadeyes.gif "[sadeyes] [sadeyes]")
Gulp!