Solved multiple choice list as a criteria in a query? (1 Viewer)

Alain CHAZOT

New member
Local time
Today, 17:55
Joined
Mar 7, 2021
Messages
8
Hello,

i have never used multiple choice list in Access

Question about multiple-choice list fields: Obviously, using the "value" property of a multiple-choice list directly as a criteria for a query does not work.

How can you easily use a multiple choice list as a criteria in a query?

In other words, how to concatenate the various selected values and put them in criteria of a query?

Should we write a function that concatenates these selected values with an "OR" between each value and use this function as a criteria?
Or use an "IN SELECT (value1, value2 ....)?


Thanks a lot for your help

Alain
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:55
Joined
Oct 29, 2018
Messages
16,612
Hi. I'm not sure you can do either of those, but I could be wrong. If so, you may have to modify the query's SQL statement to change the WHERE clause dynamically.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:55
Joined
May 7, 2009
Messages
14,176
you treat the "field" as DAO.Recordset2
supposed that you have a Form and on that form,
a Multi-Value combobox "cboKnowledge" (controlsource is "Knowledge" field, Long Integer).

the rowsource of this combo comes in another Table:

select ID, Knoweldeable from tblKnowledge;

cboKnowledge is Bound to Column 1 (ID, long).
Column Count is 2.

add command button that when clicked, will
show the enumeration of the ID's you have chosen
on this combo:

Code:
Private Sub cmdButton_Click()

    Dim parentRS As DAO.Recordset2
    Dim childRS As DAO.Recordset2
    Dim strValues As String
    Dim i As Integer
    
    If Not Me.NewRecord Then
        Set parentRS = Me.RecordsetClone
        parentRS.Bookmark = Me.Bookmark
        
        Set childRS = parentRS("Knowledge").Value
        
        With childRS
            If Not (.BOF And .EOF) Then
                .MoveFirst
            End If
            Do Until .EOF
                strValues = strValues & .Fields(0) & ","
                .MoveNext
            Loop
        End With
        
        Set childRS = Nothing
        Set parentRS = Nothing
        
        If Len(strValues) > 0 Then strValues = Left$(strValues, Len(strValues) - 1)
        
        MsgBox strValues
    End If
    
End Sub
 

Isaac

Lifelong Learner
Local time
Today, 08:55
Joined
Mar 14, 2017
Messages
5,792
I would suggest avoiding multi valued column types if at all possible. For this type of reason.
 

Users who are viewing this thread

Top Bottom