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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-22-2019, 02:08 AM   #1
droops14
Newly Registered User
 
Join Date: May 2019
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
droops14 is on a distinguished road
How to combine filters in VBA

Hi all
I've a form which has four dropdown boxes. Based on the combination of the selection in those 4 fields, I want a combined filter to work.
You can easily use an AND statement to combine the filters but you end up with issues if one of the dropdown fields is left empty as the filter will look for null values for that variable and the filter doesn't work. I now solved this by creating iff statements and redefine the filter based on whether a value is completed or not but for 3 dropdown boxes this already gives me 8 different combinations possible so by adding this fourth dropdownbox, this is not feasible anymore, I hope you understand what I mean.
Is there no easier way to combine filters that work on op of each other instead of having to define iff statements?

example:
So if I have
dropdown 1 completed
dropdown 2 completed
dropdown 3 not completed (=NULL)
dropdown 4 completed

I want the code to filter on 1, 2 and 4 and not take into account 3.
Can I use 1 statement that takes all possible combinations into account?
Thanks for your help

droops14 is offline   Reply With Quote
Old 05-22-2019, 02:21 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,336
Thanks: 158
Thanked 1,699 Times in 1,670 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: How to combine filters in VBA

I would have a look at the Allen Browne search example - it shows how to build a search form in a number of ways. http://allenbrowne.com/ser-62.html

His whole site is worth a good read !
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 05-22-2019, 02:27 AM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,834
Thanks: 107
Thanked 2,662 Times in 2,435 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: How to combine filters in VBA

Also have a look at my two example apps on building a sql string for multiple filters.
http://www.mendipdatasystems.co.uk/m...ter/4594454290

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 05-22-2019, 02:31 AM   #4
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,687
Thanks: 27
Thanked 510 Times in 483 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: How to combine filters in VBA

Untested, but this is the approach I use for any amount of controls

Need a string variable for each control
Check if the control is null and make a filter for each
Combine the individual filters
remove the AND off the end

Code:
Public function GetFilter() as string
dim strFilter as string
dim filterOne as string
dim filterTwo as string
dim filterThree as string

if not isnull(combo1) then 'Text
  filterOne = "field1 = '" & me.combo1 & "' AND "
end if

if not isnull(combo2) then 'Numeric
  filterTwo = "field2 = " & me.combo2 & " AND "
end if

if not isnull(combo3) then  'Date
  filterThree = "field3 = #" & format(me.combo3, "mm/dd/yyyy") & " AND "
end if

strFilter = FilterOne & filterTwo & filterThree
'strip off the last AND

strFilter = left(strFilter, len(strFilter) - 4)
getFilter = strFilter
end function

MajP is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Filters jparker General 4 07-28-2010 12:57 AM
How to combine two filters ? PNGBill Modules & VBA 2 05-24-2010 12:56 PM
Using filters in an ADP PeterOC SQL Server 0 03-22-2008 11:16 PM
[SOLVED] Filters K Thomas Forms 5 01-07-2004 07:46 AM
Filters Sean_Sean Forms 5 02-05-2002 12:46 PM




All times are GMT -8. The time now is 04:18 PM.


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

Featured Forum post


Sponsored Links


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