If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Convert data.
I have a table with phone number that were imported in the
following format (212)-555-0440. The field is set to text, with no input masks. I need to convert the numbers into a number field sothey are stored as follows: 2125550440. How would I formulate an Update query to do this conversion? Thanks |
#2
|
|||
|
|||
Convert data.
Robert
Keep your field as text, not numerical. If all your records are exactly the same length and format:- update to:Mid([yourfield],2,3) & Mid([yourfield],7,3) & Right([yourfield],4) If your records are not exactly the same length/format, you will probably need Instr() which locates a piece of text within another. hth Andrew L. "Robert" wrote in message ... I have a table with phone number that were imported in the following format (212)-555-0440. The field is set to text, with no input masks. I need to convert the numbers into a number field sothey are stored as follows: 2125550440. How would I formulate an Update query to do this conversion? Thanks |
#3
|
|||
|
|||
Convert data.
Or, if you can't rely on the format being identical every time:
Replace(Replace(Replace(yourString,"(",""),")","") ,"-","") Ali ----- Andrew L. wrote: ----- Robert Keep your field as text, not numerical. If all your records are exactly the same length and format:- update to:Mid([yourfield],2,3) & Mid([yourfield],7,3) & Right([yourfield],4) If your records are not exactly the same length/format, you will probably need Instr() which locates a piece of text within another. hth Andrew L. "Robert" wrote in message ... I have a table with phone number that were imported in the following format (212)-555-0440. The field is set to text, with no input masks. I need to convert the numbers into a number field sothey are stored as follows: 2125550440. How would I formulate an Update query to do this conversion? Thanks |
Thread Tools | |
Display Modes | |
|
|