CODEWITHSUNDEEP
sqlms-accesssql-update
mntyguy
mntyguy

Reputation: 187

How to UPDATE an Access Date/Time field with the current date

I'm using the following code to update a datetime field in a linked SQL table with the current date and time. However, the date that get's entered is 12/29/1899. I don't understand what I'm missing here. Any ideas?

CurrentDb.Execute "UPDATE dbo_PAYMENT SET PAYMENT_CC_DATE_PROCESSED=#" & Now & "# AND PAYMENT_CC_EMPLOYEE_ID = 0 WHERE PAYMENT_ID=" & Me.PAYMENT_ID

Upvotes: 3

Views: 18150

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123839

If you use Access SQL's built-in Now() function then you don't need to put hash marks (#) around it. (They are only required to delimit a date literal, not a date function.) Try

... SET PAYMENT_CC_DATE_PROCESSED=Now() ...

or, if the time component is not needed you can use

... SET PAYMENT_CC_DATE_PROCESSED=Date() ...

Also, if you want to update multiple fields you need to use 

... SET Field1=value1, Field2=value2 ...

not 

... SET Field1=value1 AND Field2=value2 ...

Upvotes: 4

mntyguy
mntyguy

Reputation: 187

This is it! Thanks so much @Gord Thompson!

@mntyguy Aha, I see the syntax error. If you want to update multiple fields you need to use SET Field1=value1, Field2=value2, not SET Field1=value1 AND Field2=value2

Upvotes: 1

Related Questions