How do I update these records?

[tedsmith]

Question on the design of an update query for a dynaset.

I want to increase the value of a field of a number of selected records.
This involves about 20 records in a 5000 record table and I want to do it as quickly as possible.

Eg- to increase say only the Ted records by say 1.
Eg Old New
Ted 23 24
Joe 243 243
Ted 3 4
Bill 45 45
Ted 0 1

Is there a way of writing a single update criteria expression that will do that instead of having to find each Ted record in turn to increase and update it?
I would think the engine has to somehow find out the value of each record before it can update it.

 

[JoeAtWork]

In my opinion, recordsets should only be used to retrieve data. Use a simple SQL Update statement, which can be executed with an ADO command object (a Google search will find you many code examples).

Example SQL statement:

UPDATE MyTable SET MyNumber=MyNumber+1 WHERE Name='Ted'

 

[tedsmith]

Thanks, that was what I was looking for.
I didn't realise you could use Set MyNumber=MyNumber+1