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!!
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!!