title: Access 2007: Querying for DateTime field between two Short Date values
body: <p>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:</p>
  
  <pre><code>WHERE s.studystartdatetime BETWEEN forms!frmMain!txtstartdate AND forms!frmmain!txtenddate
  </code></pre>
  
  <p>This, however, does not return rows which occurred on the enddate specified.</p>
  
  <p>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:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>Some examples of conditions I have tried:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Etc.</p>
  
  <p>Any input into this would be greatly appreciated :)</p>