使用VBA代码在访问中运行SQL语句

I can't figure out what is wrong in this, I'm collecting search criteria from a Form to use it in search.

我不知道这里面有什么问题，我正在从一个表单中收集搜索条件，以便在搜索中使用它。

The SQL line stored like this : (strWhere is the collected info from the Form)

这样存储的SQL语句:(strWhere是从表单中收集的信息)

SQLst = "SELECT Deposits.Fines, Deposits.[Deposit Value], Deposits.[Deposit Date], Deposits.Depositor, Info.Tower, Deposits.[Account Number] FROM Info, Deposits Where " & strWhere & ";"

SQLst = "选择存款。罚款,存款。(存款价值),存款。(存款日期),存款。储户信息。塔,存款。【账号】从信息中，存入“& strWhere &”;

The final SQL statement looks like this:

最后一条SQL语句如下:

SELECT Deposits.Fines, Deposits.[Deposit Value], Deposits.[Deposit Date], Deposits.Depositor, Info.Tower, Deposits.[Account Number] FROM Info, Deposits Where ([Account Number] = "1234");

选择存款。罚款,存款。(存款价值),存款。(存款日期),存款。储户信息。塔,存款。[帐户号码]从信息，存款在哪里([帐户号码]= "1234");

Now After I run the Line using this command (SQLst is the SQL Line up)

现在，在我使用这个命令运行行之后(SQLst是SQL行)

DoCmd.OpenQuery SQLst

DoCmd。OpenQuery SQLst

I get this message:

我得到这个消息:

1 个解决方案

#1

Not DoCmd.OpenQuery SQLst, that is for saved queries, not SQL strings. You need:

不是DoCmd。OpenQuery SQLst，用于保存查询，而不是SQL字符串。你需要:

' Best to use a separate instance, so you can get record counts etc.
Set db = CurrentDB

For action queries:

行动的查询:

db.Execute SQLst, dbFailOnerror

For SELECT queries, you can either use a recordset or update or create a query.

对于SELECT查询，您可以使用记录集或更新或创建查询。

Set rs = db.Openrecordset(SQLst)

' This query does not exist
Set qdf = db.CreateQueryDef("MyQuery", SQLst)

I doubt that the account number is text, so:

我怀疑账号是短信，所以:

 ([Account Number] = 1234);

Quotes are used for text-type fields / columns, numbers are as is and dates are delimited with hash (#).

引号用于文本类型的字段/列，数字为原样，日期用hash(#)分隔。