在IN语句中使用文本框值 [英] Using textbox values in IN statement
问题描述
我正在尝试使其正常工作?如何设置要在IN()
语句中使用的文本框?我希望用户能够列出他们要查询的国家/地区
I'm trying to get this to work? How can I set a textbox to be used in an IN()
statement? I would like users to be able to list out countries they'd like to query on
SELECT *
FROM Table
WHERE CountryID IN (forms!frmImport!txtCountries)
推荐答案
您不能仅将文本框的名称直接放入SQL字符串中.
相反,您需要使用文本框的内容来构建像这样的SQL字符串:
You can't just put the name of the textbox directly into the SQL string.
Instead, you need to use the content of the textbox to build a SQL string like this:
SELECT * FROM Table WHERE CountryID IN (1,2,3)
如果您的用户在文本框中输入以逗号分隔的CountryID列表,则可以这样构建SQL:
If your users enter a comma-separated list of CountryIDs into the textbox, you can build the SQL like this:
Dim SQL As String
SQL = "SELECT * FROM Table WHERE CountryID IN (" & Forms!frmImport!txtCountries & ")"
但是我不会这样做,因为它很简单,但是容易出现输入错误和SQL注入.
But I wouldn't do it this way because it's simple, but prone to input errors and SQL injection.
一种更好的方法是使用一个列表框,设置Multi Select
属性(以便可以选择多个条目),并用所有可用国家/地区的列表填充它.
A better way would be to use a list box, set the Multi Select
property (so that multiple entries can be selected) and fill it with a list of all available countries.
然后,用户可以选择所需的内容,然后可以使用列表框中的选定项来构建SQL字符串:
Then, the user can select the ones that he wants, and you can build the SQL string using the selected items from the listbox:
Dim ListItem As Variant
Dim CountryList As String
Dim SQL As String
For Each ListItem In Forms!frmImport!lstCountries.ItemsSelected
If CountryList > "" Then
CountryList = CountryList & ","
End If
CountryList = CountryList & ListItem
Next
SQL = "SELECT * FROM Table WHERE CountryID IN (" & CountryList & ")"
这篇关于在IN语句中使用文本框值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!