UPDATE query multiple rows

[illusionek]

Hi

I am trying to run below to update multiple records in the same column and get error message saying characters found after end of SQL statement. I tried to remove ; but then get a syntax error.

Please help

UPDATE [tblMonthly] SET [Date] = #20130701# where [File] = 'A';
UPDATE [tblMonthly] SET [Date] = #20130801# where [File] = 'B';

 

[plog]

An Access query isn't like other database query builders. In access you get to execute one statement at a time, not multiple ones. You're trying to do more than one so it fails when it comes to the beginning of that second statement.

My advice would be to build an update table, put the [File] values in one column and the new [Date] values in another. Link tblMonthly to this new table via the [File] fields and then update tblMonthly.File to the new date column.

 

[illusionek]

Many thanks for your suggestions, I am trying now something different that seems to be easier but for some reason dates are not updated. Any suggestions please?

CurrentDb.Execute "Update [tblMonthly] set [Date] = #20130701# where [File] = 'A';"
CurrentDb.Execute "Update [tblMonthly] set [Date] = #20130801# where [File] = 'B';"

 

[plog]

seems to be easier but for some reason dates are not updated

First, I can't but help to point out the irony in that post. Second, I don't know what to tell you, I ran a similar query in a test database and that query should work.

Did you open and close the table to refresh the data? Are you referencing correct table and field names?

 

[Minty]

Also Date is a reserved word - you shouldn't use it as a field name.
But as Plog suggested a easier way of doing this, I would try that if you are still having problems.