How to convert phone numbers???

[jmlbones]

I have a table with phone numbers represented as "(123) 456-7890". I need to convert these phone numbers in a query to appear as "123/456-7890". Can anyone help me?

 

[RickSpr]

Write a VBA function that uses string manipulation to extract the digits (using the Mid$() function) and insert them into a preformatted string (using the Mid$() = statement). Then set the query field to "=functionname(fieldname)". Rick Sprague

 

[BanditWk]

If you don't want to change the actual raw data, one of the field columns in the query would have:

Expr1: Mid([PhoneNumberField],3) & "/" & Right([PhoneNumberField],8)

HTH
Roy
aka BanditWk
Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

 

[allanon]

BanditWk I think you have a great Idea but there is a very small mistake in your query. It should read:

Expr1: Mid([PhoneNumberField], 2, 3) & "/" & Right([PhoneNumberField],8)

 

[BanditWk]

Allanon!!! Thanks, I always forget that Mid function needs another variable. jmlbones, for your information, what allanon is referring to is this:

Mid(FieldName, StartingPosition, LengthOfPosition)

I always keep forgetting that darn length... Go figure, huh? hehehehehe

Thanks for the correction!

Roy
aka BanditWk
Las Vegas, NV

 

[factotum]

Great thread. I used this technigue for something I was trying to accomplish, but I keep getting the error: "This action will reset the code in break mode."

What's that about?

Thanks.

 

[allanon]

It means that the application is running and you are changing code or something else that will cause the app to stop running. There are some things you can change and the app will continue to run. Most things you can't.

 

[factotum]

So the way out of the error is to be in SQL or Design mode, thus, the query is not running. This is normal, to be expected and can be ignored?

Thanks.

 

[allanon]

Well....first let us know what it is that you were doing when you got the error message and then we can let you know if you should ignore it.

 

[jmlbones]

Thanks all...I will give these a go.