[thelist] SQL: Infuriating Date/Time Insert

[Frank]

Re-sent 'cause I didn't see this appear the first time a couple of days ago.

I've got an infuriating problem that has been dogging me for days. I 
keep getting a syntax error, where there should be none. Have I 
missed something? I've tried variation upon variation of trying to 
insert number, a date, a time and a time. One by one analysis shows 
that the number inserts correctly. The rest all choke. Using 
CreateODBCDate() and CreateODBCTime Results in exactly the same 
error. Should a time or date be quoted (for MS Access 2k)? I've tried 
with and without with precisly the same error.

What the #^&#%! could be going on here?

Thanks and Merry Christmas.




<!--- Construct a date from the select menus (Appears as: Jan 01 2000) --->
    <cfset TheDate="#Form.Month# #Form.Day# #Form.Year#">

<!--- Insert the form data into the table
       (Datatypes: Number, Date/Time, Date/Time, Date/Time) --->

<cfquery name="InsertTimeAndDates" datasource="#datasource#">
    INSERT INTO TimeAndDates (AddrJoin, Date, StartTime, EndTime)
     VALUES(#Form.AddrJoin#, '#TheDate#', '#Form.StartTime#', '#Form.EndTime#')
    </cfquery>


Error Diagnostic Information

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO 
statement.

SQL = "INSERT INTO TimeAndDates(AddrJoin, Date, StartTime, EndTime)
        VALUES(578, 'Jan 1 2000', '8:00am', '9:00am')"

-- 

Frank Marion                      Loofah Communications
frank at loofahcom.com               http://www.loofahcom.com