使用访问 VBA 在 SQL 语句的 WHERE 子句中使用数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23301904/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using array in WHERE clause of SQL statement using access VBA
提问by vuyy1182
I have an array ListBoxContents(), it will contain the items like '15', '16','25'..upto 10 items. I'm trying to retrieve data in the column Bnumberwhere data of length >6 and starting with('15', '16','25'...) i.e those items specified in listbox .And trying to query these listbox items in where cluase of the sql statement
我有一个数组 ListBoxContents()，它将包含诸如“15”、“16”、“25”……多达 10 个项目之类的项目。我正在尝试检索Bnumber列中的数据，其中数据长度 >6 并以 ('15', '16','25'...) 开头，即列表框中指定的那些项目。并尝试查询这些列表框项目在 sql 语句的 where 子句中
Table column Bnumber contains
表列 Bnumber 包含
Bnumber
152
156
1523
16417
AA454
CC654
18A16
1826
18A16
25A76
54A16
54235A68
My VBA code
我的 VBA 代码
Private Sub arraywhere()
Dim qry As String
Dim Size As Integer
Size = Form_Input_From.lstdigits.ListCount - 1
ReDim ListBoxContents(0 To Size) As String
ReDim LContents(0 To 30) As String
Dim m As Integer
For m = 0 To Size
ListBoxContents(m) = Form_Input_From.lstdigits.ItemData(m)
Next m
For m = 0 To Size
qry = "SELECT col1,col2,Bnumber " & _
"FROM table WHERE (Len([table].[Bnumber]))>6) AND (Left
([table].[Bnumber],2))=(" & ListBoxContents(m) & ");"
Next m
Debug.Print qry
Application.CurrentDb.QueryDefs("[arrayqry]").sql = qry
DoCmd.OpenQuery "[arrayqry]"
End Sub
But my WHERE clause reads only last array item only. How do i specify array in where clause?
但是我的 WHERE 子句只读取最后一个数组项。如何在 where 子句中指定数组？
回答by Tim Williams
Try something like
尝试类似的东西
" ... ([table].[Bnumber],2)) in ('" & Join(ListBoxContents,"','") & "');"
回答by Jeremy Cook
You are setting qryto a new statement with each iteration of your for loop. Instead you need to concatenate a string based on your list box contents that will look like ("x", "y", "z")and replace =with in.
您将qry在 for 循环的每次迭代中设置一个新语句。相反，您需要根据您的列表框内容连接一个字符串，该字符串看起来像("x", "y", "z")并替换=为in.
Finish by setting your query onceit will look similar to this:
一旦它看起来与此类似，通过设置您的查询来完成：
qry = "SELECT col1,col2,Bnumber " & _
"FROM table WHERE (Len([table].[Bnumber]))>6) AND (Left
([table].[Bnumber],2)) in (" & commaSeperatedContents & ");"
Where commaSeperatedContentsis a String that is like ("x", "y", "z")but of course has your values.
commaSeperatedContents像("x", "y", "z")但当然有你的价值观的字符串在哪里。
回答by Dmitry Pavliv
Try this one:
试试这个：
Dim inPart As String
For m = 0 To Size
inPart = inPart & "'" & ListBoxContents(m) & "',"
Next m
inPart = Left(inPart, Len(inPart) - 1)
qry = "SELECT col1,col2,Bnumber " & _
"FROM [table] WHERE Len([table].[Bnumber])>6 AND " & _
"Left([table].[Bnumber],2) In (" & inPart & ");"
Debug.Print qry
CurrentDb.QueryDefs("[arrayqry]").SQL = qry
DoCmd.OpenQuery "arrayqry"
回答by Yawar
The list of items in your array actually seems to be coming from the Form_Import_From_PMT.lstdigitscontrol. Is this control bound to a data source? If so, you can simply join your tableto that data source with a join clause that specifies that only rows with Bnumbervalues starting with the digits in the joined table are to be selected:
数组中的项目列表实际上似乎来自Form_Import_From_PMT.lstdigits控件。此控件是否绑定到数据源？如果是这样，您可以简单地table使用连接子句将您的数据源连接到该数据源，该连接子句指定仅选择Bnumber值以连接表中的数字开头的行：
select col1, col2, Bnumber
from table as t
inner join tblDigits as d
on left(t.Bnumber, 2) = d.Digits
where len(t.Bnumber) > 6
If the control is notbound to a data source, then bind it now (creating a new table tblDigitsto hold the digits, as shown above), and you'll be able to use the above query.
如果控件未绑定到数据源，则立即绑定它（创建一个新表tblDigits来保存数字，如上所示），您将能够使用上述查询。
In short, data binding is how you 'use an array in a whereclause' in Access.
简而言之，数据绑定是您where在 Access 中“在子句中使用数组”的方式。
