在MS Access查询中使用多选列表框中的值

Question

我的MS Access数据库中有一个名为COA_Map的表。我还有一个带有多选列表框的表单。选定这些值后，vba会将其转换为字符串，并使用该字符串更新文本框。我想在查询中使用textbox字符串作为变量。

​

​

这是我的查询：SELECT * FROM COA_Map WHERE (COA_Map.ASL IN ( [Forms]![Multi-Select Search]![TextASL].Text ) );这将返回空结果。将文本框值复制并粘贴到查询中时，如下所示：

SELECT * FROM COA_Map WHERE (COA_Map.ASL IN ( 2.1,2.3,2.4  ) );

我得到了预期的结果。我尝试了[Forms]![Multi-Select Search]![TextASL].Value和[Forms]![Multi-Select Search]![TextASL]，但得到了一个错误：“此表达式输入错误，或者它太复杂了”

我也试着用"OR“子句代替"IN”。我更改了VBA以返回以下字符串：

​

要构建此查询：SELECT * FROM COA_Map WHERE COA_Map.ASL = [Forms]![Multi-Select Search]![TextASL] ;

这将返回相同的空结果。当我像这样将文本框值粘贴到查询中时：SELECT * FROM COA_Map WHERE COA_Map.ASL = 2.1 OR COA_Map.ASL = 2.2 OR COA_Map.ASL = 2.3 ;，我得到了预期的结果。

当在两个版本的查询中只选择一个值时，我会得到预期的结果。

我不明白为什么当从选择了多个值的文本框中读取时，查询不会返回结果。

Answer 1

这里是一个通用的例子，让你朝着正确的方向前进。

Private Sub cmdOK_Click()
' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryMultiSelect")
' Loop through the selected items in the list box and build a text string
    For Each varItem In Me!lstRegions.ItemsSelected
        strCriteria = strCriteria & ",'" & Me!lstRegions.ItemData(varItem) & "'"
    Next varItem
' Check that user selected something
    If Len(strCriteria) = 0 Then
        MsgBox "You did not select anything from the list" _
            , vbExclamation, "Nothing to find!"
        Exit Sub
    End If
' Remove the leading comma from the string
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM tblData " & _
             "WHERE tblData.Region IN(" & strCriteria & ");"
' Apply the new SQL statement to the query
    qdf.SQL = strSQL
' Open the query
    DoCmd.OpenQuery "qryMultiSelect"
' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
End Sub

​

​