Access 2007: Querying for DateTime field between two Short Date values

Question

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.

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

Etc.

Any input into this would be greatly appreciated :)

Answer 1

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.

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.

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

Answer 2

Larry's answer was the correct answer for you, but let me draw out some of the issues raised here.

1. you need to distinguish between date format and date storage. In the Jet/ACE database engine (Access's default database engine), dates are stored as an integer for the day and a decimal portion for the time. This is why you can add a digit (or a decimal) to a date and get a correct result, because the whole number part of the underlying representation of the date represents the days since Dec. 30, 1899 (the reason why it's not Dec. 31st is complicated -- somebody screwed up in calculating leap years, and so a whole bunch of programs were written with the wrong assumptions about when Dec. 31st, 1899 actually was).

2. "short date" is a date format, the standard m/d/yy (or m/d/yyyy, depending on your local settings in both Windows and Access). It has nothing to do with the actual underlying date values stored in your table, but it can have a huge effect if you work with the results of formatting. For instance, Format(Date(), "m/d/yyyy") returns a string, not a date value. It's a string that can be implicitly coerced to a date value, and one very often relies on that happening transparently. But you still have to understand that the Format() function returns a string, and that string won't always be treated as a date.

3. Jet/ACE SQL expects formatted dates to be passed in American order, the counter-intuitive m/d/yyyy, instead of the more logical d/m/yyyy or, better still, the ISO standard yyyy/m/d. Because of this, any time you are running your app with a non-US locale set for Windows, you need to be explicit about your dates. This means casting your dates to a non-ambiguous format (d/mmm/yyyy works because it specifies the day in digits and the month in letters), or process all your dates with the DateSerial() function. This applies to date criteria in your WHERE clause, or anywhere in your SELECT statement that you are doing date calculations -- pass the date into the functions in a non-ambiguous format or with DateSerial() and you will avoid this problem.