Problem with Date Entry

[kMan]

Hello All!

I am running Access 2003. Time format is set to Australian standards (ie
dd/mm/yyyy). I have a field in my table whose property is set to Medium Date.
Similarly a textbox in form, property set to Medium Date.

Im saving the form value to the table using the following technique:
strSQL = "INSERT INTO table(inDate) VALUES (" & Me.txtbox_inDate & ")"
docmd.runSQL strSQL

problem, however, is that regardless of the date I enter into the textbox,
the table always shows 30-Dec-1899. On closer inspections I can see that the
time changes. for example, entries of 1/1/07, 2/1/07, and 10/1/07 result in
30-Dec-1899 12.00.43AM, 12.01.26AM, and 12.07.10AM respectively....

What on earth am I doing wrong?

 

[Douglas J. Steele]

Two problems.

First, dates need to be delimited with # characters. Without them, the
arithmetic on 1/1/07 will be performed, so that a value of 0.142857.... will
be stored for the date field.

Second, regardless of to what your Regional Settings may have set the Short
Date format, dates must be in mm/dd/yyyy format in SQL statements (Okay,
this isn't 100% true: Access will work correctly with any unambiguous format
as well, such as yyyy-mm-dd or dd mmm yyyy. The point it, it will always try
to use mm/dd/yyyy format for nn/nn/nnnn first)

Try:

strSQL = "INSERT INTO table(inDate) VALUES (" & _
Format(Me.txtbox_inDate, "\#mm\/dd\/yyyyy\#") & ")"
docmd.runSQL strSQL

You might find it useful to read Allen Browne's "International Dates in
Access" at http://allenbrowne.com/ser-36.html, or what I have in my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be downloaded
for free at http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[kMan]

Thanks Douglas!

Two problems.

First, dates need to be delimited with # characters. Without them, the
arithmetic on 1/1/07 will be performed, so that a value of 0.142857.... will
be stored for the date field.

Second, regardless of to what your Regional Settings may have set the Short
Date format, dates must be in mm/dd/yyyy format in SQL statements (Okay,
this isn't 100% true: Access will work correctly with any unambiguous format
as well, such as yyyy-mm-dd or dd mmm yyyy. The point it, it will always try
to use mm/dd/yyyy format for nn/nn/nnnn first)

Try:

strSQL = "INSERT INTO table(inDate) VALUES (" & _
Format(Me.txtbox_inDate, "\#mm\/dd\/yyyyy\#") & ")"
docmd.runSQL strSQL

You might find it useful to read Allen Browne's "International Dates in
Access" at http://allenbrowne.com/ser-36.html, or what I have in my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be downloaded
for free at http://www.accessmvp.com/djsteele/SmartAccess.html)