Trying to filter with two fields

[Ruegen]

Trying to filter a query with two fields. One being the name of the school and the other the custom invoice number

the text field for the name is a general string

in the column I have:


like forms!frmpayments!txtFilterSchool & "*"

then I have made a custom field for the formatted calculated field called - the calculated field gets the ID but views it as "000000" format. Invoicenumber being the calculated field. Then I have duplicated it and converted it to a string field

InvNum: CStr(Format([invoicenumber],"000000"))

now I want to filter that too
Like [forms]![frmPayments]![txtInvoiceNumber] & "*"

however when I type in the school name and then search the string field InvNum I get no proper filtering on the form

any ideas?

I have tried Nz() but the custom field won't allow it

[Ruegen]

nevermind - using:

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