Results 1 to 7 of 7
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    Allow user to input multiple entries in form field to filter report

    Sorry for the confusing title. I've got a form used to filter a report of customer complaints. There's a field that lets the user input the customer ID in order to filter to just that customer's complaints.



    Is it possible to rework the below code to allow the user to input multiple customer IDs into the field? And they are seperated by semi colons (i.e. "0001;0012")

    Code:
    Private Sub btnOpenReport_Click()On Error GoTo Err_Handler
        Dim strReport As String
        Dim strDateField As String
        Dim strWhere As String
        Dim lngView As Long
        Dim strFilter As String
        Set frm = Forms!frmSearchTool
        strFilter = ""
        Const strcJetDate = "\#mm\/dd\/yyyy\#"
        strReport = Me.ComboSelectedReport
    
    
        lngView = acViewReport
            
        'Customer ID
        If Not IsNull(frm!txtCustomerID) Then
            If strWhere <> "" Then
             strWhere = strWhere & " AND "
            End If
            strWhere = strWhere & "[CustomerID] ='" & frm!txtCustomerID & "'"
        End If
    
    
        If CurrentProject.AllReports(strReport).IsLoaded Then
            DoCmd.Close acReport, strReport
        End If
        
        If IsNull(Me.txtStartDate) And IsNull(Me.txtEndDate) And IsNull(Me.txtPatientID) And IsNull(Me.txtEmployeeResponsible) And IsNull(Me.ComboLocation) And IsNull(Me.ComboCategory) And IsNull(Me.ComboSubCategory) Then Exit Sub
        
        DoCmd.OpenReport strReport, lngView, , strWhere
    
    
    Exit_Handler:
        Exit Sub
    
    
    Err_Handler:
        If Err.Number <> 2501 Then
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
        End If
        Resume Exit_Handler
    End Sub

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here you go, maybe try this (using Split() to get the individual IDs):
    Code:
    Private Sub btnOpenReport_Click()On Error GoTo Err_Handler
        Dim strReport As String
        Dim strDateField As String
        Dim strWhere As String
        Dim lngView As Long
        Dim strFilter As String
    
    
        Dim sCustomerID() as string,i As Integer "Vlad
    
    
        Set frm = Forms!frmSearchTool
        strFilter = ""
        Const strcJetDate = "\#mm\/dd\/yyyy\#"
        strReport = Me.ComboSelectedReport
    
    
    
    
        lngView = acViewReport
            
        'Customer ID
        If Not IsNull(frm!txtCustomerID) Then
    	sCustomerID=Split(frm!txtCustomerID,";")
            
    	For i = LBound(sCustomerID) To UBound(sCustomerID)
                If strWhere <> "" Then
                    strWhere = strWhere & " AND "
                End If
                strWhere = strWhere & "[CustomerID] ='" & frm!txtCustomerID & "'"
            Next i
        End If
    
    
    
    
        If CurrentProject.AllReports(strReport).IsLoaded Then
            DoCmd.Close acReport, strReport
        End If
        
        If IsNull(Me.txtStartDate) And IsNull(Me.txtEndDate) And IsNull(Me.txtPatientID) And IsNull(Me.txtEmployeeResponsible) And IsNull(Me.ComboLocation) And IsNull(Me.ComboCategory) And IsNull(Me.ComboSubCategory) Then Exit Sub
        
        DoCmd.OpenReport strReport, lngView, , strWhere
    
    
    
    
    Exit_Handler:
        Exit Sub
    
    
    
    
    Err_Handler:
        If Err.Number <> 2501 Then
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
        End If
        Resume Exit_Handler
    End Sub
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or instead of

    strWhere = strWhere & "[CustomerID] ='" & frm!txtCustomerID & "'"

    you could try


    strWhere = strWhere & "[CustomerID] IN ('" & replace(frm!txtCustomerID,";","','") & "')"

  4. #4
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Quote Originally Posted by Ajax View Post
    or instead of

    strWhere = strWhere & "[CustomerID] ='" & frm!txtCustomerID & "'"

    you could try


    strWhere = strWhere & "[CustomerID] IN ('" & replace(frm!txtCustomerID,";","','") & "')"

    I forgot to note that this is a number field. As a result, I'm getting a 3464 error due to data type mismatch. Any suggestions?

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I take it you didn't try my version?
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    forgot to note that this is a number field. As a result, I'm getting a 3464 error due to data type mismatch. Any suggestions?
    so why in your post do you have

    strWhere = strWhere & "[CustomerID] ='" & frm!txtCustomerID & "'"
    which presumable works?

    but to answer your question, remove the single quotes

    strWhere = strWhere & "[CustomerID] IN (" & replace(frm!txtCustomerID,";",",") & ")"


  7. #7
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    That worked! Thank you!

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

Similar Threads

  1. Replies: 1
    Last Post: 02-16-2018, 03:38 AM
  2. Replies: 1
    Last Post: 10-12-2017, 06:12 PM
  3. Filter report by user input?
    By Kade in forum Reports
    Replies: 1
    Last Post: 04-01-2016, 12:42 PM
  4. Replies: 2
    Last Post: 03-05-2014, 12:16 PM
  5. Filter table in form by user input
    By swavemeisterg in forum Forms
    Replies: 1
    Last Post: 06-11-2012, 02:54 PM

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