Insert a date using a runsql

[Biggles]

When I try to run the following, the date field in the table is 12/30/1899.
I know this should be simple, but I can't figure it out.

ssql = "INSERT INTO tblca_status " & _
"(txtIDEA_NO,dtmdate,txtSTATUS_CODE,memNOTES) " & _
"VALUES (" & Me!txtIDEA_NO & "," & Date & "," & "1,'INITIAL
RECORDING')"

DoCmd.RunSQL ssql

 

[John Vinson]

When I try to run the following, the date field in the table is 12/30/1899.
I know this should be simple, but I can't figure it out.

ssql = "INSERT INTO tblca_status " & _
"(txtIDEA_NO,dtmdate,txtSTATUS_CODE,memNOTES) " & _
"VALUES (" & Me!txtIDEA_NO & "," & Date & "," & "1,'INITIAL
RECORDING')"

DoCmd.RunSQL ssql

Use the required # delimiters. What is being inserted is the result of
8/30/2005, interpreted as an arithmetic expression: 1.33000831255e-4
or 11 seconds past midnight on that date (the start point of date/time
fields).

Try

ssql = "INSERT INTO tblca_status " & _
"(txtIDEA_NO,dtmdate,txtSTATUS_CODE,memNOTES) " & _
"VALUES (" & Me!txtIDEA_NO & _
",#" & Format(Date, "mm/dd/yyyy") & "# ," & _
"1,'INITIAL RECORDING')"

The literal date must be in American mm/dd/yyyy format (or an
unambiguous format) - the Format() function call just ensures that
your computer's regional settings don't mess it up.

John W. Vinson[MVP]