Yes. You can filter a multivalue field in VBA using the IN() operator function (1 Viewer)

Shananarocks

New member
Local time
Today, 21:01
Joined
Feb 25, 2020
Messages
4
I was just doing my own database and found that I can actually filter multivalue field using the IN() operator.
Basically I was filtering a multivalue field named "Workyears"
So the filter is:
rs.Filter = "[Workyears.value] IN" & "(" & wy & ")"
(since I gather that .value will string the result and a variable search will have to be found inside the string result. Hence the use of the IN() operator)

The variable "wy" (workyear) is obtained from a Combo box named Cbo_Workyear where it is bounded to Column 1 (eg. 2018, 2019, 2020, 2021 etc) where:
Dim wy As Long
wy = CLng(Me.Cbo_WorkYear)
Of course you should continue with the second recordset to filter to use the result.
Set rs_Filtered = rs.OpenRecordset()
Also both my recordsets are NOT dim as "recordset2 " (unless you are doing multivalue recordset looping as suggested by Microsoft)
Instead they remains as:
Dim rs as recordset
Dim rs_Filtered as recordset

Good Luck to those who endeavour in the crazy syntax world of Visual Basic.
Shananarocks
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 06:01
Joined
Oct 29, 2018
Messages
16,078
Hi. I'm just thinking, if wy = CLng(Me.Cbo_WorkYear), then your combo must not be a multi-select one. Is it?

If not, then you don't really need the IN() clause. rs.Filter = "[Workyears.value]=" & wy alone should work.

Just a thought...
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:01
Joined
Feb 19, 2013
Messages
13,231
suspect this will fail

"[Workyears.value] IN" & "(" & wy & ")"

no space after IN
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:01
Joined
Aug 30, 2003
Messages
35,325
no space after IN

Not needed in my experience. Happen to have an app open with this that's been working for years:

strCNG = "FuelType In(" & strCNG & ")"
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:01
Joined
Feb 19, 2013
Messages
13,231
learn something new every day
 

Users who are viewing this thread

Top Bottom