如何在 Access 2010 中为 SQL 查询提示用户输入参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13538305/
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
How to prompt user input parameters for SQL Queries in Access 2010
提问by user1848907
SELECT Transactions.TransactionNumber
FROM Transactions
WHERE (((Transactions.Date)>=#11/23/12#)) AND (((Transactions.Date)<=#11/23/12#));
The above code returns all the transaction that happened between the specified dates. But I want those dates to be defined by the user every time the query is executed.
上面的代码返回在指定日期之间发生的所有交易。但我希望在每次执行查询时由用户定义这些日期。
I tried using the [] operators to have the user define the criteria in the WHERE, something like this:
我尝试使用 [] 运算符让用户在 WHERE 中定义条件，如下所示：
WHERE (((Transactions.Date)>=[Input a Date])) AND (((Transactions.Date)<=[Input a Date]));
But microsoft Access throws a Syntax error message. The same happens when I include the # (date operators) like this
但是 microsoft Access 会抛出语法错误消息。当我像这样包含 #（日期运算符）时，也会发生同样的情况
WHERE (((Transactions.Date)>=#[Input a Date]#)) AND (((Transactions.Date)<=#[Input a Date]#));
Is there anopther way to manage dates that I'm not aware of or is asking a user for dates in a query out of the question
是否有另一种方法来管理我不知道的日期，或者在查询中询问用户日期是不可能的
回答by bonCodigo
Please,take a look at this link for user input criteria in queries
请查看此链接，了解查询中的用户输入条件
you can use query builder to do this part for you and then work fully on the SQL editor.
您可以使用查询构建器为您完成这部分工作，然后在 SQL 编辑器上全面工作。
In that case you let system help you do the format it accepts user inputs.
在这种情况下，您让系统帮助您处理它接受用户输入的格式。
回答by jtorreon
It looks like the parentheses is the cause of the syntax error. Try below.
看起来括号是语法错误的原因。下面试试。
WHERE (((Date)>=[Input a Date1])) AND (((Date)<=[Input a Date2]));
WHERE (((Date)>=[Input a Date1])) AND (((Date)<=[Input a Date2]));
回答by yanez76
Found thi post googling for a similar problem: query returnig wrong values when using between [param1] and [param2] whereas params are supposed to be date type. Access does't know nor recognize input parameters ad date but treat them as text. Me too tried coding sql staement to force recognition (and also convertion since we use dd/mm/yyyy format) with no success. I SOLVED the problem setting parameters date type in the query design view, and non need of format function or including # symbols in the sql statement
发现这个帖子有类似的问题：在 [param1] 和 [param2] 之间使用时查询返回错误值，而 params 应该是日期类型。Access 不知道也不识别输入参数广告日期，而是将它们视为文本。我也尝试编码 sql staement 以强制识别（以及转换，因为我们使用 dd/mm/yyyy 格式）但没有成功。我在查询设计视图中解决了设置参数日期类型的问题，并且不需要格式化函数或在sql语句中包含#符号
