simple "add to beginning of string" query

[Amanda Byrne]

2 projects

1: I have a query that selects all records where the phone number is less
than 9 characters (doesn't have an area code). I want to assign the same
area code to all the records in this query- i.e. I want to add "919-" to the
beginning of the string in each record.

2: Then that leaves all my data with phone numbers in the format
"919-123-4567. I want to convert them to "(919) 123-4567"- how do I go about?

Thanks, Amnada

 

[KARL DEWEY]

Update queries.
"919-" & [YourField]

"(" & [YourField]

Left([YourField],4) & ") " & Right([YourField],8)

 

[Jerry Whittle]

Not so simple. Do you have an input mask on that field at the table level? If
so, does it look something like:

!999- 000-0000;0;_
or
!999- 000-0000;;_ or !999- 000-0000;1;_

The top one actually stores the dashes in the table while the bottom two do
not. Therefor the number of characters may actually be 7 or 8 without an area
code.

 

[Amanda Byrne]

The existing table where I am using the 919-123-4567 format has no input
mask.

The table I plan to append all that data into does have an input mask for
the (919) 123-4567 formatting, and does save the phone number with this
formatting; however, this table currently has only a few records that I had
created for testing purposes (developing a new database structure), so if I
should make changes, the best time to do it is now.

thanks, Amanda

 

[Klatuu]

One mistake you are making is carrying formatted data in a table. it just
wastes space in the database. All data should be carried unformatted, and
formatted only when you want to present it visually.
There are times when you may want to use different formatting, depending on
what you want to show. In this case, you will find many different phone
number formats.
919-123-4567
(919)123-4567
(919) 123-4567
919.123.4567

 

[Amanda Byrne]

Well, removing the formatting should solve most of my problems. It should be
fairly simple to remove the dashes out of the existing data, and change the
new database so that it displays the phone numbers as it should.

Thanks! amanda

 

[Klatuu]

If you are using A2K or newer, updating the existing numbers would be easy.
Create an update query that updates the phone number in the table and use
the Replace function in the "Update To" row of the query builder:

Replace([PhoneNumber], "-", "")

This will replace all occurances of a dash to a zero length string, so
919-123-4567 becomes 9191234567