12/30/1899 saved in table instead of user input

[Jean]

i have a field of "Date/Time" type, formatted as short date and
99/99/0000;0;_ as input and no default value (null). and i have an unbound
textbox on my form for use input formatted as above. the end value saved in
the table is 12/30/1899 i checked it using an "Insert Into" statement as well
as "Update" statement. i notice when i use Date() function instead of the
Me.userinput the correct date is updated in the table.
what could possibly be the issue here?
thanks a lot.

 

[Allen Browne]

The date indicates that somewhere in your process, Access failed to
recognise the date as a date, and so it inserted the value which is
internally day zero for Access.

You need to ensure that:
a) Access understands the unbound form contains a date.
Set the Format property of the text box to General Date.

b) The date is placed correctly in the SQL statement.
Format it as mm/dd/yyyy, and delimit with #.
If it is a parameter, declare it.

For more detail on how to avoid the 3 cases where Access can get your dates
wrong, see:
http://allenbrowne.com/ser-36.html

 

[Jean]

Thanks Allen for your reply.
I found the solution. i didn't know about this in theory but i was playing
around and all is fine now.

i was using this code:
DoCmd.RunSQL "UPDATE table SET table.thedate = " & Me.inputdate & " WHERE
table.SerialNumber = " & Me.SerialNumber

The solution

 

[Allen Browne]

This would be better:

strSql = "UPDATE table SET table.thedate = #" & _
Format(Me.inputdate, "mm\/dd\/yyyy" & _
"# WHERE table.SerialNumber = " & Me.SerialNumber & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError