Problems with Date fields

[Guest]

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?

 

[Marshall Barton]

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....

The Format of a field only affects how it is displayed. It
has nothing to do with the actual value in the field.

Your resulting SQL will look like:
VALUES (18/2/2007)
which is 18 divided by 2 divided by 2007. Since that is a
very small number, you will only get a few ,monutes and
seconds in the date field. To prevent doing arithmetic on
the parts of the date, you need to enclose it in # signs.
(If you use Quotes instead of #s, then the date will be
interpreted differently depending on the machine's Locale
settings.)

The big problem is that to be safe regardless of your Locale
settings, you must format a date in an unambiguous way (i.e.
yyyy-mm-dd or mm/dd/yyyy) and it must be enclosed in #
signs.

strSQL = "INSERT INTO table(inDate) VALUES (" & _
Format(Me.txtbox_inDate, "\#m\/d\/yyyy\#") & ")"