Syntax error in UPDATE statement

[danb]

Hi, this has to be really really simple...

I get a 'Syntax error in UPDATE statement' error when executing the code below to update my Access database:

UPDATE Advert SET IsActive = -1, Date = #17/02/2004# WHERE AdvertId=" & iAdvertId

Any ideas??

Thanks.

 

[Mile-O]

"UPDATE Advert SET IsActive = -1, Date = #17/02/2004# WHERE AdvertId = " & iAdvertId & ";"

Hard coding a date into an UPDATE query isn't advisable - means rework every time you want to use it.

 

[danb]

Right, yeah, it's not actually hard-coded in my real page. I'll explain:

I'm building a feature for inputting classified adverts for second-hand vehicles. A user needs to input their details, which will be written to a database alongside an expiry date (calculated as being 14 days from the current day). I realise I could input the dates as today's date, then do the 14 days part in the SELECT of the vehicle listings page - but it's a bit more complicated than that, so I'm looking to insert a date into the Access date field.

The actual code I'm using (with your tweaks) is:

"UPDATE Advert SET IsActive = -1, Date = #" & dFinalExpiry & "# WHERE AdvertId = " & iAdvertId & ";"

Still no joy?!?

It works fine when I remove the date part.

 

[Mile-O]

I can't see anything else other than the fact you have a field called Date which is a reserved word in Access.

 

[danb]

By jove, that's done it! Thanks very much... I shall beware of that one!

 

[Mile-O]

Be careful...it's not the only Reserved Word.

Part One (Access) and Part Two (JET)