I have a table that contains some data like this...

Phone_Numbers
212-560-5602
212-587-2938

And I have an Excel document that contains data like this...

phone_numbers
(211) 787-3957
(217) 827-2999

How can I import the Excel data into the Access Database so that they are
treated
both as phone numbers? I also do not want to allow duplicates.

Please help!

Run an update like this:

Update MyTable
Set [PhoneNum] = Replace(Replace([PhoneNum],"(",""),") ","-")

The inside Replace is removing the left paren. The outside Replace removes
the right paren and space.

You might want to store just the numbers and use formatting and input masks
for the user. That way you stay flexible as to how th enumber displays.

--
Bill Mosca, MS Access MVP


"SinCity" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>I have a table that contains some data like this...
>
> Phone_Numbers
> 212-560-5602
> 212-587-2938
>
> And I have an Excel document that contains data like this...
>
> phone_numbers
> (211) 787-3957
> (217) 827-2999
>
> How can I import the Excel data into the Access Database so that they are
> treated
> both as phone numbers? I also do not want to allow duplicates.
>
> Please help!
>
>
>

Bill,

Forgive me (I'm a newbie).....but how exactly do I run the following command
that you suggested?


Update MyTable
Set [PhoneNum] = Replace(Replace([PhoneNum],"(",""),") ","-")


If you wouldn't mind telling me what to point and click on I would
appreciate it.
(Access 2003)

Thx,
Mark




"Bill Mosca, MS Access MVP" <(E-Mail Removed)> wrote in message
news:uq$(E-Mail Removed)...
> Run an update like this:
>
> Update MyTable
> Set [PhoneNum] = Replace(Replace([PhoneNum],"(",""),") ","-")
>
> The inside Replace is removing the left paren. The outside Replace removes
> the right paren and space.
>
> You might want to store just the numbers and use formatting and input
> masks for the user. That way you stay flexible as to how th enumber
> displays.
>
> --
> Bill Mosca, MS Access MVP
>
>
> "SinCity" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>>I have a table that contains some data like this...
>>
>> Phone_Numbers
>> 212-560-5602
>> 212-587-2938
>>
>> And I have an Excel document that contains data like this...
>>
>> phone_numbers
>> (211) 787-3957
>> (217) 827-2999
>>
>> How can I import the Excel data into the Access Database so that they are
>> treated
>> both as phone numbers? I also do not want to allow duplicates.
>>
>> Please help!
>>
>>
>>
>
>

No problem, Mark.

Open a new query in design view. When the box pops up to pick a table, close
it without picking anything. Click on View>SQL view.

Paste what I gave you into the blank window.
Change MyTable to the name of your table.
Change PhoneNum to the name of your phone field.
Click the ! icon to run the query.

--
Bill Mosca, MS Access MVP


"SinCity" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Bill,
>
> Forgive me (I'm a newbie).....but how exactly do I run the following
> command that you suggested?
>
>
> Update MyTable
> Set [PhoneNum] = Replace(Replace([PhoneNum],"(",""),") ","-")
>
>
> If you wouldn't mind telling me what to point and click on I would
> appreciate it.
> (Access 2003)
>
> Thx,
> Mark
>
>
>
>
> "Bill Mosca, MS Access MVP" <(E-Mail Removed)> wrote in message
> news:uq$(E-Mail Removed)...
>> Run an update like this:
>>
>> Update MyTable
>> Set [PhoneNum] = Replace(Replace([PhoneNum],"(",""),") ","-")
>>
>> The inside Replace is removing the left paren. The outside Replace
>> removes the right paren and space.
>>
>> You might want to store just the numbers and use formatting and input
>> masks for the user. That way you stay flexible as to how th enumber
>> displays.
>>
>> --
>> Bill Mosca, MS Access MVP
>>
>>
>> "SinCity" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>>I have a table that contains some data like this...
>>>
>>> Phone_Numbers
>>> 212-560-5602
>>> 212-587-2938
>>>
>>> And I have an Excel document that contains data like this...
>>>
>>> phone_numbers
>>> (211) 787-3957
>>> (217) 827-2999
>>>
>>> How can I import the Excel data into the Access Database so that they
>>> are treated
>>> both as phone numbers? I also do not want to allow duplicates.
>>>
>>> Please help!
>>>
>>>
>>>
>>
>>
>
>

Hi Bill,

Thanks again for your past post. It worked beautifully. I am making some
other changes to my database that are similar to the one that you helped me
with. Would you mind helping me understand the following statement?

Set [PhoneNum] = Replace(Replace([PhoneNum], "(" , "" )," ) "
,"-")

Your syntax does seem to work, but it also seems a little different then the
syntax I found on the web.

Thanks much!

We're using a replace function inside of another replace function. I did
that because we have 2 pieces of the original string that need work. The
open parenthesis must be removed and the closed paren and one space must be
replaced with a dash.

When you nest functions, the inside one is run first, then the outside one.
There is no real order necessary here. Either function could have been on
the inside. If I used one function at a time, I would need to run 2 separate
queries.

Replace is a built-in function with 3 parameters.
1. The string you want to change.
2. The piece you want changed.
3. The piece you want the change to be.

In the inside Replace function:
Replace([PhoneNum], "(" , "" )

[PhoneNum] is the string.
( is the character to search for.
An empty string (2 double quotes with nothing in the middle) is what we want
put in. In other words, we want to replace an open parenthesis with nothing.
So "(555) 555-1235" becomes "555) 555-1234"

Once the open paren is removed, we can replace the close paren and one space
with a dash. Now "555) 555-1234" becomes 555-555-1234.

That help?

--
Bill Mosca, MS Access MVP


"SinCity" <(E-Mail Removed)> wrote in message
news:u27S$(E-Mail Removed)...
> Hi Bill,
>
> Thanks again for your past post. It worked beautifully. I am making some
> other changes to my database that are similar to the one that you helped
> me
> with. Would you mind helping me understand the following statement?
>
> Set [PhoneNum] = Replace(Replace([PhoneNum], "(" , "" )," ) " ,"-")
>
> Your syntax does seem to work, but it also seems a little different then
> the
> syntax I found on the web.
>
> Thanks much!
>
>
>
>

>
> That help?
>
> --
> Bill Mosca, MS Access MVP

Thanks Bill. Your explanation was awesome! Yes that helped.