Solved multiple choice list as a criteria in a query?

[Alain CHAZOT]

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]

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]

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:

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

 

[Gasman]

Croos posted https://www.accessforums.net/showthread.php?t=83132

 

[Isaac]

I would suggest avoiding multi valued column types if at all possible. For this type of reason.

 

[Alain CHAZOT]

Thanks a lot guys

;-)

Alain