SQL code: UDATE..SET date values?

[jadeIT]

This is a common and simple problem many people (beginners like me) seem to have... but i couldnt find anything to help my specific problem on the forums so..

I am using an SQL statement in a form's code like this (simplified):

SQL = "UPDATE tableName " & _
"SET date = #" & sDate & "# " & _
"WHERE status = 0"

Where sDate is a variable of date type.

When running the code, i get the error "Syntax error in UPDATE statement". As i didnt get this error before when I wasn't updating an actual date field, I'm assuming I'm entering the code wrongly.

I also tried entering the date literally, eg:

SQL = "UPDATE itemsToInvoice " & _
"SET date = #07/07/07# " & _
"WHERE done = 0"

But still the same error.

I'm pretty sure im just typing it wrongly, but what am I doing wrong?

 

[Travis]

Try this:

Put "DEBUG.PRINT SQL" after the line where you set the SQL String variable.

This will print the SQL string in the immediate window allowing you to cut and paste it into the SQL view of a new Query.

Try running the Query from here. It will more clearly point out the error in the SQL statement.

 

[jadeIT]

Figured it out

Yeah i tried it out, and although i got the same error msg, it highlights the part of the SQL statement that is causing the problem.

It turned out that it wasnt the way I was entering the date after all, but the fact that the field i was inserting the value into was called 'date'. I had to retype it to 'tablename.date' and it worked!

thanks

 

[Rich]

Date is a reserved word in Access, you should re-name the field, it will continue to cause you problems if you don't

 

[Travis]

What you just saw using the SQL in your Query is why I always recommend viewing SQL from code in your Query window when you are having problems.

 

[jadeIT]

Rich you were right, as when i went back to using the SQL in a form's code, it continued to cause errors once again, so I renamed the field to 'invoiceDate' rather than just 'date.

using queries to figure out problems in SQL statements also is very helpful.

I've learnt heaps from this board already.. its the best!