Hi, everyone. I have a question that someone can hopefully help me with.

I have a textfield called PHONE in my access 2000 database. The database has
already been populated with data. Each record has 10 digits in the phone
field representing a phone number with an area code. Is there a way to write
an update query that will automatically put dashes in after the area code
and the first three numbers?

Any suggestions are helpful because I really don't want to do this all by
hand! ;)

--Ashley

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.377 / Virus Database: 211 - Release Date: 7/15/2002
 

You can put a mask in the cell description [Access] and when you just plug 
in numbers it will format them through a forms pane.  I have not tried this 
using ASP to plug the number in, but might be worth a shot.  Breaking up the 
number with string functions while a little more complex would work to.
Regards,
Chuck




"Whoever said the pen is mightier than the
sword obviously never encountered automatic
weapons."

http://68.43.100.7:81/aa8vs





>From: "Young, Ashley" <Ashley.Young@c...>
>Reply-To: "Access ASP" <access_asp@p...>
>To: "Access ASP" <access_asp@p...>
>Subject: [access_asp] Automatically Put Dashes in Phone Number?
>Date: Mon, 30 Dec 2002 14:58:45 -0500
>
>Hi, everyone. I have a question that someone can hopefully help me with.
>
>I have a textfield called PHONE in my access 2000 database. The database 
>has
>already been populated with data. Each record has 10 digits in the phone
>field representing a phone number with an area code. Is there a way to 
>write
>an update query that will automatically put dashes in after the area code
>and the first three numbers?
>
>Any suggestions are helpful because I really don't want to do this all by
>hand! ;)
>
>--Ashley
>
>---
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.377 / Virus Database: 211 - Release Date: 7/15/2002
>
>


_________________________________________________________________
The new MSN 8: smart spam protection and 3 months FREE*. 
http://join.msn.com/?page=features/junkmail&xAPID=42&PS=47575&PI=7324&DI=7474&SU= 
http://www.hotmail.msn.com/cgi-bin/getmsg&HL=1216hotmailtaglines_smartspamprotection_3mf

Hi,

If I were you I would do as follows:

	Add a new field to your existing table (let's say PHONE2)
	Create an update query which the SQL looks something like this:

UPDATE tbl_data SET tbl_data.PHONE2 = Left([tbl_data]![PHONE],2) & "-" &
Right((Left([tbl_data]![PHONE],5)),3) & "-" & Right([tbl_data]![PHONE],5);
(watch for wrapping)

where:

	tbl_data is the name of you table
	PHONE is your existing field
	PHONE2 is the field you created

After you run the query, the fields will look like this:

	|    phone    |     phone2  	|
	| 1234567890  |  12-345-67890 |
	| 0987654321  |  09-876-54321 |

Then, just delete column phone and rename phone2 to phone.

If you want to do it from ASP, you just have to set up the connection
string, use the same SQL and delete/rename the column when your done.

HTH

Paulo Fernandes
Consernet, Lda.
www.consernet.com
Telefone: +xxx xxx xxx xxx
Telemóvel: +xxx xxx xxx xxx
Fax: +xxx xxx xxx xxx
----------------------------
Este e-mail é confidencial. Se você não é o destinatário pretendido deste
e-mail,não deverá usar qualquer informação nele contida. Se recebeu este
e-mail por erro, envie-o para administrador@c..., apagando-o de
seguida. Obrigado.

This email is confidential. If you are not the intended recipient, you must
not disclose or use the information contained in it. If you have received
this mail in error, please forward it to administrador@c... and
delete the document. Thank you.

-----Mensagem original-----
De: Young, Ashley [mailto:Ashley.Young@c...]
Enviada: segunda-feira, 30 de Dezembro de 2002 19:59
Para: Access ASP
Assunto: [access_asp] Automatically Put Dashes in Phone Number?


Hi, everyone. I have a question that someone can hopefully help me with.

I have a textfield called PHONE in my access 2000 database. The database has
already been populated with data. Each record has 10 digits in the phone
field representing a phone number with an area code. Is there a way to write
an update query that will automatically put dashes in after the area code
and the first three numbers?

Any suggestions are helpful because I really don't want to do this all by
hand! ;)

--Ashley

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.377 / Virus Database: 211 - Release Date: 7/15/2002