The CreateODBCDate is probably the better option, but if you know your form field is a valid date format (valid from Access's perspective), then you can do this:
Insert (DateField) Values (###Form.DateField###) The first two pounds indicates a date to Access, and the next is used as a standard CF #. My two cents... Shawn Grover -----Original Message----- From: Howie Hamlin [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 9:00 AM To: CF-Talk Subject: Re: Insert Date/Time Format into MS Access DB 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.cf m >>> 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 > ______________________________________________________________________ Get Your Own 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=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
