Access Date Parameters - Date Range Prompt Error

Question

I have an Access 2007 report that prompts for a range of dates. It is using the SQL Query:

SELECT Calls.CallID, Contacts.County, Calls.ContactID, Calls.Date, Calls.Subject, Calls.Notes, Calls.Time FROM Contacts INNER JOIN Calls ON Contacts.[ContactID] = Calls.[ContactID] WHERE (((Calls.Date) Between [From date: ] And [To date: ]));

This works for most dates but I am getting an error when using the dates 07/01/2009 and 06/14/2010.

This 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. (Error 3071)

I have confirmed that none of the Calls.Date values are null. Is there some better way to prompt the user for the date range?

Answer 1

The best way to get parameters from a user is with a form. You can refer to the form in the query, for example:

Between Forms!frmDates!FromDate And Forms!frmDates!ToDate

It will make life easier for you, because you can evaluate the input before running the query. It will also make life a lot easier for the user, in that you can take advantage of the calendar that attaches to date data types in Access 2007 & 2010.