Formatting Phone Numbers to drop symbols

[Guest]

I am importing external data from a .txt file in which the phone numbers are sent (999) 999-9999. My database is set to use the numbers as straight 9999999999. Any ideas on input masks or formatting. I import the data into a table, from there I run a query to build a new table in which my .mdb is set to.

Should I be formatting in the quey or in the import. AND HOW

Thanks for the help,

 

[John Nurick]

Hi Louis,

If you're using Access 2002 or 2003, or an up-to-date installation of
Access 2000, use the Replace() function in the append query. Something
like this:

fNumber: Replace(Replace(Replace(Replace([Phone], "(", ""), ")", ""), "
",""), "-", "")

 

[Guest]

Hello Joh
Thank you for your feedback. Unfortunately I do not use an append Query. After the table is produced, I Select all and add data to current table

Any other suggetsions?

 

[Guest]

Where would I put this Replace function in an append query?

 

[tina]

build the append query. when it's set up, then go to the phone number column
in the QBE grid (lower half of query design window). in the Field row (first
row at top of grid), replace the name of the phone number field with the
expression that John gave you. make sure you substitute the name of the
phone number field in place of [Phone] in the expression (keeping the
brackets, as [MyPhoneFieldName]).

hth

 

[tina]

well, i've never used the Replace() function. i just understood *where* John
intended you to use it. the error message seems to indicate that Access does
not recognize the function, though.
his post does say "an up-to-date installation of Access 2000", so it could
be that you don't have the necessary Service Pack - but i'm just guessing. i
put John's name in the Subject line; maybe he'll see it and help us out, or
someone else will.

Tina
Thank you very much foro your helpful feedback. The append query is

built, but when I replace the field name [phone] with the epression, I am
getting the following error message. Any ideas? It is Access 2000.

 

[Douglas J. Steele]

Open the immediate window (using Ctrl-G) type the following:

?Replace("abc", "b", "c")

then hit Enter. Do you get acc back, or an error?

If you get acc back, then the Replace function would appear to be working
correctly, and your problem may be that you haven't applied all of the
service packs (the original version of Access 2000 wouldn't let you use the
Replace function in queries, but the problem was fixed in one of the service
packs).

To get you going, create a custom function that "wraps" the built-in Replace
function:

Function MyReplace(Expression As Variant, Find As Variant, Replace As
Variant) As Variant
MyReplace = Replace(Expression, Find, Replace)
End Function

Go back to your query, and use MyReplace instead of Replace.

If, on the other hand, you didn't get acc back, but got an error instead,
you might have a problem with your References.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

Open any code module and select Tools | References from the menu bar.
Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Tina
Thank you very much foro your helpful feedback. The append query is

built, but when I replace the field name [phone] with the epression, I am
getting the following error message. Any ideas? It is Access 2000.

 

[tina]

thanks, Doug - the original question came from Louis, of course, but this
helped me also!

Open the immediate window (using Ctrl-G) type the following:

?Replace("abc", "b", "c")

then hit Enter. Do you get acc back, or an error?

If you get acc back, then the Replace function would appear to be working
correctly, and your problem may be that you haven't applied all of the
service packs (the original version of Access 2000 wouldn't let you use the
Replace function in queries, but the problem was fixed in one of the service
packs).

To get you going, create a custom function that "wraps" the built-in Replace
function:

Function MyReplace(Expression As Variant, Find As Variant, Replace As
Variant) As Variant
MyReplace = Replace(Expression, Find, Replace)
End Function

Go back to your query, and use MyReplace instead of Replace.

If, on the other hand, you didn't get acc back, but got an error instead,
you might have a problem with your References.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

Open any code module and select Tools | References from the menu bar.
Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Tina
Thank you very much foro your helpful feedback. The append query is

built, but when I replace the field name [phone] with the epression, I am
getting the following error message. Any ideas? It is Access 2000.

"Undefined function 'Replace' in expression"

Thank you,
Louis

 

[Guest]

Thanks alot to both Doug and Tina. I really appreciate the extra efforts. Its been a great learning curve.

 

[Guest]

Tina & Doug & Joh
I got it! Thanks to both of you. I needed to update my service packs, when I did it worked
Thanks again.

 

[John Nurick]

Thanks for catching this, Doug.

Open the immediate window (using Ctrl-G) type the following:

?Replace("abc", "b", "c")

then hit Enter. Do you get acc back, or an error?

If you get acc back, then the Replace function would appear to be working
correctly, and your problem may be that you haven't applied all of the
service packs (the original version of Access 2000 wouldn't let you use the
Replace function in queries, but the problem was fixed in one of the service
packs).

To get you going, create a custom function that "wraps" the built-in Replace
function:

Function MyReplace(Expression As Variant, Find As Variant, Replace As
Variant) As Variant
MyReplace = Replace(Expression, Find, Replace)
End Function

Go back to your query, and use MyReplace instead of Replace.

If, on the other hand, you didn't get acc back, but got an error instead,
you might have a problem with your References.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

Open any code module and select Tools | References from the menu bar.
Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)