Update Field

[locksp]

Hi,
I have a table with a field that contains a four digit number. What I would like to do is select the numbers that begin with 6***. Then I would like to make them 5 digit numbers so they become 26***. Is there any way I can do this. Selecting with wildcards OK, updating seems to be the problem.

Regards

Steve

 

[JamesMcS]

Yep, you can update it in an update query. Drag your field down and put 6* in your criteria, and 2&[fieldname] in the update to box. The & concatenates values together in Access, you can use it everywhere.

PS if it's a string and not a number you'll have to put "2" & [fieldname] in.

 

[locksp]

Thank you. It worked a treat!!

 

[JamesMcS]

No probs, have a good one!

 

[DCrake]

Did not discover if it was text or number but if numeric you would need to add 20000 to the existing number

X:Field+20000

because if the number was 600 and you prefixed it with a 2 it would become 2600 and if 2600 already exists then you would have a duplicate.

 

[JamesMcS]

What a dummy I am. Good thing you're here to show us the light DC!