Access query help needed !!

[titli]

I want to run a update query

accApp.OpenCurrentDatabase sRawDatabaseName
accApp.DoCmd.TransferText acImportDelim, "DataImportSpec", "tblRaw", sLocalDataFile, False
accApp.DoCmd.RunSQL  "Update tblRaw SET tblData.MaturityDate="" WHERE BookCode="123-America" AND StrategyID IN('420','320','220');"

This sRawDatabaseName is C:\Documents and Settings\All Users\Documents\Data.mdb

Access doesn't accept this SQL

"Update tblRaw SET tblData.MaturityDate="" WHERE BookCode="123-America" AND StrategyID IN('420','320','220');"

Whereas I run it , by saving the intermediate database and it works fine .I run in that database manually

Update tblData SET MaturityDate=""  WHERE BookCode="123-America" AND StrategyID IN('420','320','220');

Please help immediately if possible.Many thanks in advance !!

[NeoPa]

Your setting of :

MaturityDate=""

will not work as you have it from within a SQL string. It will see that as :

MaturityDate="

See Quotes (') and Double-Quotes (") - Where and When to use them.

[thelonelyghost]

First off, for the sake of clarity, don't be afraid to use the [code] boxes in this forum.

Onto your issue. Your SQL is in the form of a string. What this means is that Access chokes when you use quotes in the way you did:

"Update tblRaw SET tblData.MaturityDate="" WHERE BookCode="123-America" AND StrategyID IN('420','320','220');"

It currently sees it as three separate strings:

Update tblRaw SET tblData.MaturityDate=" WHERE BookCode=
123-America
 AND StrategyID IN('420','320','220');

The above doesn't even have any sort of concatenation symbol ( & ) so Access panics and doesn't know what to do. If you change it to this, it may solve your issue:

"Update tblRaw SET tblData.MaturityDate='' WHERE BookCode='123-America' AND StrategyID IN('420','320','220');"

EDIT: Nice link, NeoPa! I didn't know it existed, but c'est tres utile!

[NeoPa]

Thanks LePhantasmSeul :)

We have a bunch of articles on the site in Access. I try to link to them where I can. It saves reinventing the wheel every time, which can get a little tedious.

PS. I just got back from Paris at the weekend so my French is as good as it gets ATM :D

[titli]

Thanks All ...for your responses and explanations....Yes, the last update query in the link , worked fine.

[NeoPa]

Thanks for the response Titli. Glad that helped.

I'm going to set post #3 as best as the explanation there is the clearest and fullest.