Vba update query

[eka24]

please I want to update the following values in field1 from table1.

Grade1 To Grade 11
Grade2 To Grade 12
Grade3 To Grade 13

I am able to handle single value but not as many as listed above.

All the values are in the same field1 to be updated in the same field1.

 

[Minty]

If you put the old and new values into a table then a single set based update query can achieve this.

 

[eka24]

I am replacing the old values with the new ones. Example replace Grade1 with Grade11, Grade2 with Grade12

 

[Minty]

Yes. I understand that.

If you need to do this for a large number of values then create a table with the two fields OldGrade and NewGrade.

Now create a query that has your original data and the new table in it. Link the existing grade to the OldGrade field. Select ExistingGrade from your original table and the NewGrade as the fields to display, then run the query - what do you see?

Now change that to an upgrade query. And put the NewGrade field into the UpgradeTo part of the query.

 

[bastanu]

If this a one time deal and you don't need to ever do it again (in which case you should follow Minty's advice) you can simply open the table, select the entire column, press CTRL+H and use replace all (replace "Grade" with "Grade1").

Cheers,
Vlad

 

[eka24]

I have been able to achieve what i wanted upon some search with;
UPDATE Table1 SET Table1.Field1 = Switch([Field1] Like "Grade1","Grade2",[Field1] Like "Grade2"," Grade3");

I need help on how to achieve same using VBA
Thanks in advance

 

[Cronk]

I think

update Table1 set Table1.Field1 = left(Field1,5) & ' 1' & mid(Field1 ,6)

is more succinct, and I would follow Minty's suggestion of adding a new temporary field to hold the updated data in case there is a data stuff up because you have overlooked some exception. (I've learned the hard way)

As to vba,

currentdb.execute "update Table1 set Table1.Field1 = left(Field1,5) & ' 1' & mid(Field1 ,6)"

or whatever sql string you choose to use