Updating "date" field in ACCESS from a vb form

[carlos]

I'm not sure if I'm missing something here.
I'm tring to update the "Date" field in a record in an ACCESS database, using a form I designed in vb.net.
Everything else works fine but as soon as I try to send the date to the access form, I get an error saying there is a Syntax error in UPDATE statement.

As soon as I remove the part of the commandtext with date in it, it works absolutely fine. I have tried sending the date to ACCESS as a string rather than a date, but still no joy.

Here is the code I used:

objCmd.CommandText = "UPDATE tblPNS SET Off = '" & _
strOff & "', Spa = '" & m_Spa & "', Hre = '" & _
m_Hre & "', Date = '" & m_Date & _
"' WHERE Off = '" & strOff & "'"

The first field is a string, the next two are integers, the last is a date.

I looked through the books and the forum first but couldn't find what I was doing wrong here. Can anyone point me in the right direction?

 

[Paszt]

Enclose the date value in pound signs (#, Shift+3) instead of single quotes.

 

[carlos]

Thanks for the suggest. I managed to solve this last night - my stupidity: The column in the ACCESS database was named"Date" but it seems that "Date" is verboten even within the protected confines of a command query.
I changed the column name to "InvDate" and lo and behold it all worked out fine.
I will try the pound signs instead of single quotes too.

 

[Paszt]

Of course, should have picked up on that.
It's always good practice not to use certain reserve words, but if you find it's too late to change a field name for some reason, you can also enclose that field name in brackets (ex: [Date]) in some cases.

Here's a list of some reserved words in Access 2002 and Access 2003