You need to use the CreateODBCDate() function in your insert like:
<CFQUERY NAME="INSOBJ"
DATASOURCE="uspc">
INSERT INTO t_sectest
(cal_date_start,
cal_date_end)
VALUES ('#createodbcdate(form.cal_date_start)#',
'#createodbcdate(form.cal_date_end)#')
Remeber - the formatting settings in Access are for presentation and do not affect the
internal structure of the table. A date/time
field is really just a floating point number.
HTH,
--
Howie Hamlin - inFusion Project Manager
On-Line Data Solutions, Inc. - www.CoolFusion.com - 631-737-4668 x101
inFusion Mail Server (iMS) - The Intelligent Mail Server
<<< Vote for iMS as the CFDJ Reader's Choice Awards Most Innovative Product >>>
http://www.sys-con.com/coldfusion/readerschoice2001/nominationformbpa.cfm
>>> Find out how iMS Stacks up to the competition:
>http://www.coolfusion.com/imssecomparison.cfm
----- Original Message -----
From: "Mark Leder" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, January 02, 2002 10:54 AM
Subject: Insert Date/Time Format into MS Access DB
> I want to insert a date from a form, as MM/DD/YYYY
> I set up an SQL Statement as follows:
>
> <CFQUERY NAME="INSOBJ"
> DATASOURCE="uspc">
> INSERT INTO t_sectest
> (cal_date_start,
> cal_date_end)
> VALUES ('#form.cal_date_start#',
> '#form.cal_date_end#')
>
> </cfquery>
>
> If I set the MS Access db field as Date/Time, even with a mask of
> 99/99/0000;0;_ - it throws a syntax error. (I've tried it with and without
> single quotes surrounding the variable).
>
> If I set the Access db field as Text, it does take the date, but then does
> not sort properly (based on ascending or descending dates).
>
> My question, what is the correct way to format the SQL statement so that I
> can insert the form.cal_date_start as a Date/Time Field in MS Access.
>
> Thanks in advance,
>
> Mark
>
______________________________________________________________________
Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
