Date showing as 12/30/1899

[btrini10]

strSQL = "INSERT INTO CASE_CONTACT1 (CASE_ID, CONTACT_ID, DATE_ADDED) VALUES (" & Me.CASE_ID & "," & Me.CONTACT_ID & "," & Format(strDate, "yyyy-mm-dd") & ")"

I am trying to get today's date into the Date_added field. Using the above I get 6/8/1905.

Using below I get 12/30/1899.

strSQL = "INSERT INTO CASE_CONTACT1 (CASE_ID, CONTACT_ID, DATE_ADDED) VALUES (" & Me.CASE_ID & "," & Me.CONTACT_ID & "," & Date & ")"

Can someone assist me in identifying what I am doing wrong?

Thanks

 

[SkipVought]

Hi,

Dates are just NUMBERS.

12/30/1899 is ZERO.

strSQL = "INSERT INTO CASE_CONTACT1 (CASE_ID, CONTACT_ID, DATE_ADDED) VALUES (" & Me.CASE_ID & "," & Me.CONTACT_ID & "," & Format([b]Date()[/b], "yyyy-mm-dd") & ")"

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Use either:
strSQL = "INSERT INTO CASE_CONTACT1 (CASE_ID, CONTACT_ID, DATE_ADDED) VALUES (" & Me.CASE_ID & "," & Me.CONTACT_ID & ",#" & Format(Date, "yyyy-mm-dd") & "#)"

or:
strSQL = "INSERT INTO CASE_CONTACT1 (CASE_ID, CONTACT_ID, DATE_ADDED) VALUES (" & Me.CASE_ID & "," & Me.CONTACT_ID & ",Date())"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[btrini10]

Thanks for the response guys, but I must be doing something wrong. when I try Date(), the brackets are being removed when I close my code.
PHV, when I try your first solution I get syntax error in the INSERT INTO.

Am I doing something wrong?

Thanks

 

[randy700]

When you use Date, are you referring to the reserved word or a field in your table? If it's a field in your table, use square brackets [Date].

Better yet, don't use reserved words as field names!


Randy