Access 2007：在两个Short Date值之间查询DateTime字段 [英] Access 2007: Querying for DateTime field between two Short Date values

问题描述

我有一个包含两个文本框的表单供用户输入。两个文本框都将属性格式设置为短日期。一个是开始日期，另一个是结束日期。我也有几个表，每个都有一个DateTime字段（studystartdatetime）。我想能够查询这些表，但将结果限制为DateTime字段在输入日期（包括）之间的行。目前，条件是：

I have a form containing two text boxes for user input. Both text boxes have the Property format set to "Short Date". One is the "start date", and the other is the "end date". I also have several tables, each with a DateTime field ("studystartdatetime"). I would like to be able to query these tables, but restrict the results to rows whose DateTime fields are between the entered dates (inclusive). Currently, the condition is:

WHERE s.studystartdatetime BETWEEN forms!frmMain!txtstartdate AND forms!frmmain!txtenddate

但是，这不会返回指定的结束日期发生的行。

This, however, does not return rows which occurred on the enddate specified.

我试过了CDate，Format和DateValue的每个组合，我可以想到在其中包装一个或所有这些字段，但我总是收到相同的隐含错误：

I have tried every combination of CDate, Format, and DateValue that I could think of in which to wrap one or all of these fields, but I always receive the same cryptic error:

表达式键入不正确，或者太复杂，无法评估。例如，数字表达式可能包含太多复杂的元素。尝试通过将表达式的部分分配给变量来简化表达式。

The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

我尝试过的一些条件示例：

Some examples of conditions I have tried:

WHERE CDate(Format(s.studystartdatetime, "yyyy/mm/dd")) BETWEEN forms!frmMain!txtstartdate AND forms!frmmain!txtenddate

WHERE DateValue(Format(s.studystartdatetime, "yyyy/mm/dd")) BETWEEN forms!frmMain!txtstartdate AND forms!frmmain!txtenddate

WHERE CDate(Format(s.studystartdatetime, "yyyy/mm/dd")) BETWEEN CDate(Format(forms!frmMain!txtstartdate, "yyyy/mm/dd")) AND CDate(Format(forms!frmmain!txtenddate, "yyyy/mm/dd"))

WHERE DateValue(Format(s.studystartdatetime, "yyyy/mm/dd")) BETWEEN CDate(Format(forms!frmMain!txtstartdate, "yyyy/mm/dd")) AND CDate(Format(forms!frmmain!txtenddate, "yyyy/mm/dd"))

WHERE DateValue(Format(s.studystartdatetime, "Short Date")) BETWEEN forms!frmMain!txtstartdate AND forms!frmmain!txtenddate

其他。

输入到这将非常感谢：）

Any input into this would be greatly appreciated :)

推荐答案

发生什么是，你的短日期输入生成datetime值在午夜在用户输入的开始日期。因此，范围2009-1-1到2009-1-10（或您的系统上使用的任何短日期格式）是从1月1日开始到开始的搜索事件1月10日，并排除1月10日晚些时候发生的活动。

What's happening is that your short date inputs are producing datetime values at midnight on the start of the day the user entered. So, the range 2009-1-1 to 2009-1-10 (or whatever short date format is used on your system) is searching for events from the very start of January 1st to the very start of January 10th and excluding the events that happened later on January 10th.

要更正，请在用户输入搜索结束日期时加1。这将从1月1日开始到1月11日开始搜索，包括1月10日的所有活动。

To correct, add 1 to the end date the user puts into the search. This will search from the very start of January 1st to the very start of January 11th, including all events on the 10th of January.

最后，发生在 >正确的 1月11日午夜可以以这种方式进入您的结果，因此您应该使用

Finally, events that occurred at exactly midnight of January 11th can slip in to your results this way, so instead of using BETWEEN you should use

studystartdatetime> = forms！frmMain！txtStartDate AND studystartdatetime< forms！frmMain！txtEndDate + 1

studystartdatetime >= forms!frmMain!txtStartDate AND studystartdatetime < forms!frmMain!txtEndDate + 1

这篇关于Access 2007：在两个Short Date值之间查询DateTime字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！