How to save date to Access2000 DB 1

[oceandeep]

I just encounted a ploblem to save date field to Access database. It is like:<br>Page 1:<br>&lt;cfform action=&quot;DateTestAct.cfm&quot; method=&quot;post&quot;&gt;<br>&nbsp;&lt;cfinput type=&quot;text&quot; size=&quot;20&quot; name=&quot;DateTx&quot;&gt;<br>&nbsp;&lt;input type=&quot;submit&quot; name=&quot;Submit&quot; value=&quot;Submit&quot;&gt;<br>&lt;/cfform&gt;<br><br>action page for page 1:<br>&lt;cfquery datasource=&quot;aaa&quot; name=&quot;DateTest&quot;&gt;<br>insert into DateTest<br>(TestDate)<br>values(#DateTx#);<br>&lt;/cfquery&gt;<br><br>DB: DateTest, TestDate(Type)Date/Time)<br>I have defined the default value of TestDate to be null. <br>If I enter date like &quot;01/01/2000&quot; in the form, no problem, the database can accept, but the actual date it saved was &quot;1899-12-31&quot;.<br>And if I didn't key in anything in the form, then CF will reject an error message to me.<br>So how can I overcome it?<br>Thanks a lot!<br>

 

[DarkMan]

Try this on the action page:<br><FONT FACE=monospace><b><br>&lt;cfif isdate(DateTx)&gt;<br>&nbsp;&nbsp;&lt;cfquery datasource=&quot;aaa&quot; name=&quot;DateTest&quot;&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;insert into DateTest<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(TestDate)<br>&nbsp;&nbsp;&nbsp;&nbsp;values<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(#dateformat(DateTx,&quot;mm/dd/yyyy&quot#);<br>&nbsp;&nbsp;&lt;/cfquery&gt;<br>&lt;cfelse&gt;<br>&nbsp;&nbsp;No date was input.<br>&lt;/cfif&gt;<br></b></font><br>If you're inputting the date with other fields, you can do this:<br><FONT FACE=monospace><b><br>&lt;cfquery datasource=&quot;aaa&quot; name=&quot;DateTest&quot;&gt;<br>&nbsp;&nbsp;insert into DateTest<br>&nbsp;&nbsp;&nbsp;&nbsp;(field1,field2&lt;cfif isdate(DateTx)&gt;,TestDate&lt;/cfif&gt<br>&nbsp;&nbsp;values<br>&nbsp;&nbsp;&nbsp;&nbsp;(#field1#,field2#&lt;cfif isdate(DateTx)&gt;,#dateformat(DateTx,&quot;mm/dd/yyyy&quot#)&lt;/cfif&gt;;<br>&lt;/cfquery&gt;<br></b></font><br><br>The database itself will save the date in an odbc format so when you're outputting the date you should use the dateformat function like:<br><FONT FACE=monospace><b><br>&lt;cfoutput&gt;#dateformat(DateTx,&quot;mm/dd/yyyy&quot#&lt;/cfoutput&gt;<br></b></font><br>Hope this helps....<br><br>DM

 

[oceandeep]

Thanks a lot!<br>This first method I tried, and it worked!<br>But for the second method is what I want, unfortunately, it doesn't work, the problem is that the database can not accept an empty value for date type field.<br>If I directly key in data in MS Access2000, I can leave blank for the date field and finally it will be filled with NULL, so how come I couldn't insert NULL to it from CF?<br>So headache!<br>Some good idea can help me out?<br>Thanks in advance!

 

[oceandeep]

Hi, DarkMan:<br>Please just forgot my post above.<br>I want to tell you a good news, your second method does worked!! I just add another field in database and then it worked!<br>I am so glad and thank you very much for your kindly help!

 

[DarkMan]

Just a quick note, the field names I used are just example names, you need to change them (or add or take away database fields) to suit your needs.&nbsp;&nbsp;Also, I forgot to include a # in front of the #field2# in the values list (I had field2#)......