Results 1 to 2 of 2
  1. #1
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496

    Trying to filter with two fields

    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

  2. #2
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filter fields including records with null
    By Ruegen in forum Programming
    Replies: 18
    Last Post: 01-28-2014, 11:23 PM
  2. Fields repeat in column filter
    By thepuppyprince in forum Access
    Replies: 2
    Last Post: 01-16-2013, 01:42 AM
  3. Filter on one or more fields using a dialog box
    By jparker1954 in forum Reports
    Replies: 21
    Last Post: 07-15-2011, 03:39 PM
  4. Filter By Date In Different Fields
    By Douglasrac in forum Queries
    Replies: 13
    Last Post: 03-21-2011, 05:24 PM
  5. Replies: 3
    Last Post: 10-07-2010, 09:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums