I am using CF8 and an Access 2000 database that contains the following fields:
- ID (autonumber)
- EventDate2 (date/time)
- EventTime (text)
- School (text)
- Event (memo)
- Description (memo)
My form page contains 3 form fields, the School (drop down), the start date (text) and the end date (text).
Whenever I try to query my database by school AND between the 2 date ranges, I get no results, and yet there are plenty of dates in the database that meet the search requirements. Here is my SQL statement.
<cfquery name="q_Cal" datasource="#DataSource#">
WHERE Location = '#SchoolDrop#' AND EventDate2 BETWEEN #startdate# and #enddate#
From some reading I have done, it appears that I need to convert my dates to ODBC compatible dates using the CreateODBCDate function. But this function takes 3 parameters (year, month, day) and my date fields are in short date formats, e.g. 01/01/2008. How can I convert my dates to ODBC format using only the short date format?