Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-10-2018, 12:58 AM   #1
rockovo
Newly Registered User
 
Join Date: Nov 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
rockovo is on a distinguished road
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".

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!!
Attached Images
File Type: jpg Grade filter issue.jpg (53.0 KB, 11 views)
File Type: jpg Grade filter issue 2.JPG (20.6 KB, 6 views)
File Type: jpg Grade filter issue 4.JPG (59.2 KB, 6 views)

rockovo is offline   Reply With Quote
Old 11-10-2018, 01:21 AM   #2
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 554
Thanks: 24
Thanked 5 Times in 5 Posts
MickJav is on a distinguished road
Re: Multiple combo boxes to filter list results including text and numbers

Have you used a number datatype when it should be a string?
__________________
After 20 years working with access i have no more hair to give.
MickJav is offline   Reply With Quote
Old 11-10-2018, 01:25 AM   #3
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 554
Thanks: 24
Thanked 5 Times in 5 Posts
MickJav is on a distinguished road
Re: Multiple combo boxes to filter list results including text and numbers

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

__________________
After 20 years working with access i have no more hair to give.
MickJav is offline   Reply With Quote
Old 11-10-2018, 03:29 AM   #4
rockovo
Newly Registered User
 
Join Date: Nov 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
rockovo is on a distinguished road
Re: Multiple combo boxes to filter list results including text and numbers

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?
Attached Images
File Type: jpg Grade filter issue 5.jpg (58.1 KB, 5 views)
rockovo is offline   Reply With Quote
Old 11-10-2018, 03:32 AM   #5
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,609
Thanks: 309
Thanked 404 Times in 389 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Multiple combo boxes to filter list results including text and numbers

Debug.Print FiltStr in the code or the immediate window?
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 11-10-2018, 03:36 AM   #6
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 554
Thanks: 24
Thanked 5 Times in 5 Posts
MickJav is on a distinguished road
Re: Multiple combo boxes to filter list results including text and numbers

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
__________________
After 20 years working with access i have no more hair to give.

Last edited by MickJav; 11-10-2018 at 04:10 AM.
MickJav is offline   Reply With Quote
The Following User Says Thank You to MickJav For This Useful Post:
rockovo (11-16-2018)
Old 11-11-2018, 01:49 AM   #7
rockovo
Newly Registered User
 
Join Date: Nov 2018
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
rockovo is on a distinguished road
Re: Multiple combo boxes to filter list results including text and numbers

Thanks guys, it's sorted now. Removed quotes around Grade so it read it as numbers not string and it worked


rockovo is offline   Reply With Quote
Reply

Tags
combobox , dropdown , filters , vba access

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Search using Combo Box and Filter the results in text boxes comep Visual Basic 11 08-13-2017 06:20 AM
Need help - Refresh list boxes and using multiple text boxes in search form thaonguyen Forms 0 10-02-2013 08:50 PM
Multi Combo Boxes - Filter results, but show all when blank. macca72 Forms 2 09-01-2011 11:39 PM
I cant get my query results to list in the text boxes! Officeboy Forms 2 08-20-2010 07:16 AM
Combo Box Drop List Including Multiple Fields RodShinall Forms 7 05-02-2009 03:10 AM




All times are GMT -8. The time now is 08:09 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World