Vba update query (1 Viewer)

eka24 · Feb 22, 2018

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 · Feb 22, 2018

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

eka24 · Feb 22, 2018

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

Minty · Feb 22, 2018

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 · Feb 22, 2018

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 · Feb 23, 2018

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 · Feb 24, 2018

I think

Code:

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,

Code:

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

or whatever sql string you choose to use

Vba update query (1 Viewer)

eka24

Registered User.

Minty

AWF VIP

eka24

Registered User.

Minty

AWF VIP

bastanu

AWF VIP

eka24

Registered User.

Cronk

Registered User.

Similar threads

Users who are viewing this thread