vba 将文本框值插入到 Access SQL 查询中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10008215/
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
Insert textbox value into Access SQL query
提问by Peraklo
Being an amateur in Access and VBA, I've hit a wall with the following...
作为 Access 和 VBA 的业余爱好者，我遇到了以下问题...
In MS Access 2007, I have the following query:
在 MS Access 2007 中，我有以下查询：
SELECT .... WHERE format(NZ(l_f_date),'yyyy-mm-dd')<=**'2012-04-03'**);
I have shortened it a bit of course.
我当然缩短了一点。
The database has approx 20 queries that need to be run on a daily basis. I have created 4 macros to run groups of queries in the manner that I need them to be run. The problem is that in every one of those queries I first have to change the date (like in the upper query). I am looking for the way to automate it a bit more.
该数据库有大约 20 个需要每天运行的查询。我创建了 4 个宏来以我需要它们运行的方式运行查询组。问题是，在每一个查询中，我首先必须更改日期（就像在上面的查询中一样）。我正在寻找更多自动化的方法。
I have an idea to create a Form, place a button for every macro on it and 2 textbox-es to enter the 2 dates I need. Now, I need those dates to appear in the bolded part. As I think about it, I have 2 options:
我有一个想法来创建一个表单，为它上面的每个宏放置一个按钮和 2 个文本框来输入我需要的 2 个日期。现在，我需要这些日期出现在粗体部分。仔细想想，我有两个选择：
- Create a temporary table in the database to store those two dates and pull those 2 fields in my queries.
- Insert the value of the textbox somehow directly into the bolded part of the query.
- 在数据库中创建一个临时表来存储这两个日期并在我的查询中提取这两个字段。
- 以某种方式将文本框的值直接插入查询的粗体部分。
I think I can manage the first solution, but the second one is making my head hurt.
我想我可以管理第一个解决方案，但第二个让我头疼。
Can you help?
你能帮我吗？
SQL from comment
来自评论的 SQL
select proc_desc,count(s) as broj
into upit3
from ( select distinct a.case_id as s,
a.org_case_id,a.act_date as day,d.prod_id,d.prod_desc,
c.fname,c.dpd,c.due_amount,proc_id,proc_desc
from actions_b as a, cases_old_b as c,processes_b as p,
product_dict_b as d
where a.org_case_id=c.[org_ case_id]
and a.act_date=Forms!Form!Text10 and d.prod_id=c.product
and p.proc_id=c.process and not_lead=1 )
group by proc_desc order by proc_desc;
OK, sample data....
好的，样本数据....
In x.dates, value is exactly like this: 03.04.2012
In a.act_date value is like this: 01.07.2011 13:53:56
在 x.dates 中，值是这样的：03.04.2012
在 a.act_date 中，值是这样的：01.07.2011 13:53:56
so if its not possible with this values as they are, is it possible to use a LIKE statement in the query? Pseudo: WHERE a.act_date LIKE x.date%
If its possible, how to use it? i am a novice in sql and access queries... I google but sometimes, like this time, i get stuck.
Thanks
因此，如果无法使用这些值，是否可以在查询中使用 LIKE 语句？伪： WHERE a.act_date LIKE x.date%
如果可以，如何使用？我是 sql 和访问查询的新手...我谷歌但有时，就像这次，我卡住了。
谢谢
采纳答案by Peraklo
OK, so i decided to create a simple 2-column table in my database that will be used just for storing yesterdays and todays date... i added 2 text-boxes to a form, 1 button and added the next procedure to the button:
好的，所以我决定在我的数据库中创建一个简单的 2 列表，该表将仅用于存储昨天和今天的日期……我在表单中添加了 2 个文本框、1 个按钮并将下一个过程添加到按钮中：
Private Sub Command25_Click()
CurrentDb.Execute "DELETE * FROM Datumi"
Dim tbl As Recordset
Set tbl = CurrentDb.OpenRecordset("Datumi")
tbl.AddNew
tbl!brojka = "1"
tbl!datum = Text8.Value
tbl.Update
tbl.AddNew
tbl!brojka = "2"
tbl!datum = Text10.Value
tbl.Update
End Sub
As you can see, the click on the button will clear the table and store new values into 2 rows... row 1 is yesterday, row 2 is today...
如您所见，单击该按钮将清除表格并将新值存储到 2 行中……第 1 行是昨天，第 2 行是今天……
And after that im pulling the values from that table like this:
在那之后，我从该表中提取值，如下所示：
... where x.brojka=1 and format(a.act_date,'yyyy-mm-dd')=format(x.datum,'yyyy-mm-dd') ...
I'm sure it can be done better but this one works for me...
我相信它可以做得更好，但这个对我有用......
回答by Fionnuala
This structure:
这种结构：
SELECT .... WHERE format(NZ(l_f_date),'yyyy-mm-dd')<='2012-04-03');
Is not a good idea. The general rule is to try and stick to field (column) names on the left side of the equals. So
不是个好主意。一般规则是尝试并坚持使用等号左侧的字段（列）名称。所以
SELECT .... WHERE l_f_date <=#2012-04-03#
Nulls will not be included and I hope your dates aredates and not strings.
将不包括空值，我希望您的日期是日期而不是字符串。
Next add the form:
接下来添加表单：
SELECT .... WHERE l_f_date <= Forms!TheNameOfTheForm!TheNameOfTheTextbox
EDIT re comments
编辑重新评论
You are using the query design window, yes? Please try this test query:
您正在使用查询设计窗口，是吗？请试试这个测试查询：
SELECT a.case_id, a.act_date
FROM actions_b AS a
WHERE a.act_date=Forms!Form!Text10
