Results 1 to 3 of 3

Multiple combo boxes to filter list results including text and numbers

  1. #1
    rockovo is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    2

    Multiple combo boxes to filter list results including text and numbers

    Hi all,

    I am very new to Access and programming at all so I'll appreciate all the support.
    Just completed one of those online courses and I am currently working on a little project based on material from the course to learn more, but I got stuck for a few days now and cannot find answer anywhere as this sort of issue wasn't covered.
    I created a list of employees in a form and 5 combo box filters that narrow down displayed results in conjunction with each other.
    4 out of 5 filters work perfectly fine but as soon I select value from "Grade" filter which is in numerical values I get "Run-time Error '3464': Data type mismatch in criteria expression".

    Click image for larger version. 

Name:	Grade filter issue.jpg 
Views:	12 
Size:	53.0 KB 
ID:	36156Click image for larger version. 

Name:	Grade filter issue 2.JPG 
Views:	12 
Size:	20.6 KB 
ID:	36157

    When I press "Debug" it takes me to:

    Click image for larger version. 

Name:	Grade filter issue 3.JPG 
Views:	12 
Size:	108.1 KB 
ID:	36158Click image for larger version. 

Name:	Grade filter issue 4.JPG 
Views:	12 
Size:	59.2 KB 
ID:	36159


    I understand the problem is that Grade values should be captured as Integer rather than String but I don't know how to set it up so all the filters work together.
    The idea is that whoever would use it, they could use random number and combination of the filters to display groups of employees they're interested in.
    I would very much appreciate any advice and if possible example of the code I should use.
    Example of my code is below:

    Private Sub GradeFilt_GotFocus()
    Me.AllowEdits = True
    If Nz(FiltStr, "") = "" Then
    Me.GradeFilt.RowSource = "SELECT DISTINCT Employees.Grade FROM Employees;"
    Else
    Me.GradeFilt.RowSource = "SELECT DISTINCT Employees.Grade FROM Employees WHERE" & FiltStr & ";"
    End If
    Me.GradeFilt.Dropdown
    End Sub


    Private Sub GradeFilt_LostFocus()
    Me.AllowEdits = False
    End Sub





    Sub BuildFiltStr()
    FiltStr = ""
    If Me!NameFilt <> "" Then
    FiltStr = "[LastName] = '" & Me!NameFilt & "'"
    End If
    If Me!RoleFilt <> "" Then
    If FiltStr = "" Then
    FiltStr = "[Role] = '" & Me!RoleFilt & "'"
    Else
    FiltStr = FiltStr & " AND [Role] = '" & Me!RoleFilt & "'"
    End If
    End If
    If IsNumeric(Me!GradeFilt) Then
    If FiltStr = "" Then
    FiltStr = "[Grade] = '" & Me!GradeFilt & "'"
    Else
    FiltStr = FiltStr & " AND [Grade] = '" & Me!GradeFilt & "'"
    End If
    End If
    If Me!DeptFilt <> "" Then
    If FiltStr = "" Then
    FiltStr = "[Dept] = '" & Me!DeptFilt & "'"
    Else
    FiltStr = FiltStr & " AND [Dept] = '" & Me!DeptFilt & "'"
    End If
    End If
    If Me!ShiftFilt <> "" Then
    If FiltStr = "" Then
    FiltStr = "[Shift] = '" & Me!ShiftFilt & "'"
    Else
    FiltStr = FiltStr & " AND [Shift] = '" & Me!ShiftFilt & "'"
    End If
    End If
    NameStr = Nz(Me!NameFilt, "")
    RoleStr = Nz(Me!RoleFilt, "")
    GradeStr = Nz(Me!GradeFilt, "")
    DeptStr = Nz(Me!DeptFilt, "")
    ShiftStr = Nz(Me!ShiftFilt, "")


    If FiltStr = "" Then
    Me.Filter = ""
    Me.FilterOn = False
    Else
    Me.Filter = FiltStr
    Me.FilterOn = True
    End If
    End Sub




    THANK YOU!!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,948
    grades seem to be numbers and you are trying to use a string.
    remove the quotes:

    ="[Grade] =" & Me!GradeFilt

  3. #3
    rockovo is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    2
    Quote Originally Posted by ranman256 View Post
    grades seem to be numbers and you are trying to use a string.
    remove the quotes:

    ="[Grade] =" & Me!GradeFilt

    Thank you ranman256!! It worked

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

Similar Threads

  1. Filter List Box Using text boxes
    By Shamli in forum Access
    Replies: 1
    Last Post: 11-05-2018, 02:23 PM
  2. Combo Box Filter from List Box Results
    By UTLee in forum Access
    Replies: 2
    Last Post: 08-16-2017, 06:29 PM
  3. Filter with multiple Combo boxes
    By dotcanada in forum Access
    Replies: 3
    Last Post: 09-01-2016, 12:19 PM
  4. Filter form from multiple combo boxes
    By Bird_FAT in forum Programming
    Replies: 6
    Last Post: 05-19-2010, 08:32 AM
  5. Replies: 1
    Last Post: 03-01-2009, 08:53 AM

Tags for this Thread

Posting Permissions

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