Multiple combo boxes to filter list results including text and numbers (1 Viewer)

rockovo

New member
Local time
Today, 01:52
Joined
Nov 10, 2018
Messages
5
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".

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.
I've attached screenshots and 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!!
 

Attachments

  • Grade filter issue.jpg
    Grade filter issue.jpg
    53 KB · Views: 126
  • Grade filter issue 2.JPG
    Grade filter issue 2.JPG
    20.6 KB · Views: 113
  • Grade filter issue 4.JPG
    Grade filter issue 4.JPG
    59.2 KB · Views: 110

Dreamweaver

Well-known member
Local time
Today, 01:52
Joined
Nov 28, 2005
Messages
2,466
Have you used a number datatype when it should be a string?
 

Dreamweaver

Well-known member
Local time
Today, 01:52
Joined
Nov 28, 2005
Messages
2,466
If IsNumeric(Me!GradeFilt) Then
If FiltStr = "" Then
FiltStr = "[Grade] = '" & Me!GradeFilt & "'"
Else
FiltStr = FiltStr & " AND [Grade] = '" & Me!GradeFilt & "'"
End If
End If


looks like you asked if numeric but still used a sting still reading more
 

rockovo

New member
Local time
Today, 01:52
Joined
Nov 10, 2018
Messages
5
Thank you MickJav for quick reply!

All the variables I have declared in this form are:
Dim SrchVal As String
Dim SrchCrit As String
Dim LastFld As String
Dim FiltStr As String

I used your code and pasted it in place of GradeFilt in Sub BuildFiltStr as in attached screenshot.
Still getting same run-time error however this time when I press Debug it takes me to Me.FilterOn = True (highlighted in screenshot).

It looks to me like it doesn't recognise the value in this dropdown as FiltStr...is that correct?
 

Attachments

  • Grade filter issue 5.jpg
    Grade filter issue 5.jpg
    58.1 KB · Views: 108

Gasman

Enthusiastic Amateur
Local time
Today, 01:52
Joined
Sep 21, 2011
Messages
14,048
Debug.Print FiltStr in the code or the immediate window?
 

Dreamweaver

Well-known member
Local time
Today, 01:52
Joined
Nov 28, 2005
Messages
2,466
I didn't change it but have below All changes are red:


If IsNumeric(Me!GradeFilt) Then
If FiltStr = "" Then
FiltStr = "[Grade] = " & Me!GradeFilt
Else
FiltStr = FiltStr & " AND [Grade] = " & Me!GradeFilt
End If
End If
 
Last edited:

rockovo

New member
Local time
Today, 01:52
Joined
Nov 10, 2018
Messages
5
Thanks guys, it's sorted now. Removed quotes around Grade so it read it as numbers not string and it worked :)
 

Users who are viewing this thread

Top Bottom