update/remove character from field

[Ruegen]

I have phone numbers with ( ) around the first two digits



I am hoping to remove them so

instead of (02) 5555 5555

I get 02 5555 5555

any suggestions?

[ssanfu]

Use the replace function.

If the control name is Ph_Number, then you could use

Replace(Replace(Ph_Number, "(", ""), ")", "")

in a select query or update query.

[Ruegen]

Use the replace function.

If the control name is Ph_Number, then you could use

Replace(Replace(Ph_Number, "(", ""), ")", "")

in a select query or update query.

Yeah I thought about using replace - but that replaces the whole field yes?

How about only the chosen characters leaving the numbers intact?

Set tblName.field = replace(field,(")","")

would replace the fields but would it leave the numbers in tact?

[ssanfu]

Yeah I thought about using replace - but that replaces the whole field yes?

No. One of the arguments is find, which is Required. It is the substring being searched for.

Note that the example I gave has nested replace functions. The inner replace replaces the opening parenthesis, then the outer replace replaces the closing parenthesis.

Replace "Ph_Number" with the name of your field.



Did you try it in a select query????????



Once you see that it works, you can change the select query to an update query and replace all of the opening/closing parenthesis at once.

Set tblName.field = replace(field,(")","")

would replace the fields but would it leave the numbers in tact?

Nope.... there is an extra opening parenthesis... you will get a syntax error.

You can do this in code, but it is a lot slower.

you will have to open a recordset (lets call it rs)
move to the first record
do while not rs.EOF
  rs.edit
  rs("Ph_number) = Replace(Replace(Ph_Number, "(", ""), ")", "")
  rs.Update

rs.movenext
Loop
.
.
rest of code to close the recordset and clean up

Change [Ph_Number] to your field name

[Ruegen]

No. One of the arguments is find, which is Required. It is the substring being searched for.

Note that the example I gave has nested replace functions. The inner replace replaces the opening parenthesis, then the outer replace replaces the closing parenthesis.

Replace "Ph_Number" with the name of your field.



Did you try it in a select query????????



Once you see that it works, you can change the select query to an update query and replace all of the opening/closing parenthesis at once.


Nope.... there is an extra opening parenthesis... you will get a syntax error.

You can do this in code, but it is a lot slower.

you will have to open a recordset (lets call it rs)
move to the first record
do while not rs.EOF
  rs.edit
  rs("Ph_number) = Replace(Replace(Ph_Number, "(", ""), ")", "")
  rs.Update

rs.movenext
Loop
.
.
rest of code to close the recordset and clean up

Change [Ph_Number] to your field name

I asked just to come to an understanding of the process - I needed to know why I would use the method you mentioned so that I can apply it in the future for other things

I'll now give it a go

thanks for your help - will update later with the result

like now if I wanted to change just the inner ( and leave the outer ) then I know I can use

replace( "(","")

or any other character...

[ssanfu]

replace( "(","")

Don't forget to add the expression (ie field). => Replace(experssion, "(","")

You can replace more than just one character.
If you had a field that contained "What kind of car is that? Is that a Ford?", you could use the replace function like this:

MyField = Replace(MyField, "Ford","Chevy")

and the result would be "What kind of car is that? Is that a Chevy?"

It is a very powerful function.

Good luck...

[Ruegen]

Don't forget to add the expression (ie field). => Replace(experssion, "(","")

You can replace more than just one character.
If you had a field that contained "What kind of car is that? Is that a Ford?", you could use the replace function like this:

MyField = Replace(MyField, "Ford","Chevy")

and the result would be "What kind of car is that? Is that a Chevy?"

It is a very powerful function.

Good luck...

thanking you kindly! worked perfectly