Changing the format of data

[Guest]

Hello,
In a table I have a field in which Telephone numbers are entered. But they
are not all in the same format. Some are entered: 210-1234567, others are
entered: 2101234567 and others: 21012-34567. What I need is to delete the: "
- " from every entry. So that 210-1234567 will become 2101234567. Note that
I don't want it to become 210 space 1234567 but 2101234567. and note that the
" - " is not always in the same place for example after the third digit. I
just want the - to dissapear from any entry that it is entered.
I hope I explained it well cause I don't speak very good english.

Thank you
Dimitris

 

[Guest]

Use the Replace() function. In an update query put something like below in
the Update To row under the Telephone Field. Please put in the correct field
name.:

Replace([Telephone Field],"-","")

Your English is fine.

 

[Guest]

And to avoid the spaces:

Replace(Replace([Telephone Field],"-",""), " ", "")
--
Dave Hargis, Microsoft Access MVP

Use the Replace() function. In an update query put something like below in
the Update To row under the Telephone Field. Please put in the correct field
name.:

Replace([Telephone Field],"-","")

Your English is fine.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,
In a table I have a field in which Replace("210-1234567 ","-","") numbers are entered. But they
are not all in the same format. Some are entered: 210-1234567, others are
entered: 2101234567 and others: 21012-34567. What I need is to delete the: "
- " from every entry. So that 210-1234567 will become 2101234567. Note that
I don't want it to become 210 space 1234567 but 2101234567. and note that the
" - " is not always in the same place for example after the third digit. I
just want the - to dissapear from any entry that it is entered.
I hope I explained it well cause I don't speak very good english.

Thank you
Dimitris

 

[Guest]

Thank you Dave and Jerry for your help.
Problem solved

And to avoid the spaces:

Replace(Replace([Telephone Field],"-",""), " ", "")
--
Dave Hargis, Microsoft Access MVP

Use the Replace() function. In an update query put something like below in
the Update To row under the Telephone Field. Please put in the correct field
name.:

Replace([Telephone Field],"-","")

Your English is fine.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,
In a table I have a field in which Replace("210-1234567 ","-","") numbers are entered. But they
are not all in the same format. Some are entered: 210-1234567, others are
entered: 2101234567 and others: 21012-34567. What I need is to delete the: "
- " from every entry. So that 210-1234567 will become 2101234567. Note that
I don't want it to become 210 space 1234567 but 2101234567. and note that the
" - " is not always in the same place for example after the third digit. I
just want the - to dissapear from any entry that it is entered.
I hope I explained it well cause I don't speak very good english.

Thank you
Dimitris

 

[Guest]

I just realized there is a problem.
Some entries have two "-" inserted. I just want the first "-" to dissapear
the second one should stay as it is. That is in a few records but is there a
way to fix it?

Thank you
Dimitris

 

[Pieter Wijnen]

don't remember which parameter of the top of my head, but replace can be
instructed to replace the first entry only

HTH

Pieter

 

[Guest]

If you look in VBA Help your will find you can define that start position and
the number of replacements to make.