nevermind - using:
Code:
Function filterPayments()
Dim strwhere As String
Dim lngLen As Long
Dim strSchool As String
Dim strInvoice As String
If Not IsNull(Me.txtFilterSchool) Then
strSchool = Me.txtFilterSchool.Value
strSchool = Replace(strSchool, "'", "''")
strwhere = strwhere & "([SchoolName] Like '*" & strSchool & "*') AND "
End If
If Not IsNull(Me.txtInvoiceNumber) Then
strInvoice = Me.txtInvoiceNumber.Value
strInvoice = Replace(strInvoice, "'", "''")
strwhere = strwhere & "([invnum] Like '*" & strInvoice & "*') AND "
End If
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strwhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
'clears text boxes
Me.txtFilterSchool = Null
Me.txtInvoiceNumber = Null
Me.Filter = strOriginalPaymentsFilter
Me.FilterOn = True
Else 'Yep: there is something there, so remove the " AND " at the end.
strwhere = Left$(strwhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strOriginalPaymentsFilter & " and " & strwhere
Me.FilterOn = True
End If
End Function