CODEWITHSUNDEEP
sqlvbams-access
Scotch
Scotch

Reputation: 3226

Update Date anomoly

As I was adding some functionality to a form, it required that I update a date for a row. My update query was built out to be:

   "update tblReview set tblreview.[reviewDate] = me.currdate WHERE empID = '"& me.empid & "';"

(basically) it was printed out to be

  "Update tblReview set tblreview.[reviewdate] = 7/24/2012 WHERE empID = '1';"

Cool, that's what I wanted. Now lets look at my data

 *empId*       *reviewDate*
    1             12/30/1899

What?! Why does it do this? Okay, sure, I should probably put '#' characters around my date to fix it. But why does it update to this ridiculous value rather than giving me some sort of error?

Upvotes: 1

Views: 65

Answers (2)

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32680

You tried setting it to 7 divided by 24 divided by 2012 days.

You need:

"update tblReview set tblreview.[reviewDate] = '" & _
    Format(me.currdate, "yyyy-mm-dd") & "' WHERE empID = " & me.empid & " ;"

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91316

That should be:

"update tblReview set tblreview.[reviewDate] =#" _
& Format( me.currdate,"yyyy/mm/dd") _
& "# WHERE empID = " & me.empid

Upvotes: 4

Related Questions