Removing Formatting Symbols from Phone numbers

[Guest]

A project that I am doing requires collecting large amounts of contact data
and working with the phone numbers. My job would be a lot easier if the
formatting on the phone numbers were deleted. For example, the left and
right parentheses, Slashes, dashes and spaces cause some problems and I would
like to know how to remove them. I am not knowledgable in writing code but
do know a few things about queries. Everything I try results in a syntax
error message. What can you suggest?

 

[Guest]

Is this a one time update? If so, just open the table in datasheet view and
use Edit->Replace.

 

[Guest]

I have tried this before. I receive the error message that "Microsoft Can't
find the text you specified in the Find What Box." These are symbols that
Access uses in code which is why I find it difficult to use queries to delete
them. I have to defer to someone with more advanced knowledge than myself
for this problem. I would think there would be some knowledge base articles

 

[raskew via AccessMonster.com]

Hi -

Search on 'chopit'. You'll find sample code to accomplish what you're after.


HTH - Bob

 

[Guest]

You didn't answer my question. Also I'm not sure what you mean by "symbols
that Access uses in code". Are you using Input Masks in your table designs.
IMHO get rid of them.

 

[John Spencer]

You can use a VBA function to do this. Paste the function below into a
module and save it the module (Don't name the module the same as the
function)

If you want to do this permanently then use the function in an Update query.
If you just want to do this to see the value then you can use the function
in the select clause

SELECT fStripToNumbersOnly([Telephone]) as PhoneNum
FROM YourTable

IF you have a lot of records this could be slow.

The update query would look like
UPDATE YourTable
SET [Telephone] = fStripToNumbersOnly([YourTable].[Telephone])
WHERE [Telephone] is Not Null


Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As Variant
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = varText
Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I collect contact data from various sources on the internet. From the
different sources, this data is stored then accumulated into a larger
database file. What I want to do is remove these symbols from the phone
number field () / -. The reason being some sources input their data as (999)
999-9999, others 999/999-9999, other 999-999-9999. This impedes my ability
to work with this data especially when trying to sort sequentially.

Access sends syntax error messages when these symbols are not used properly
in VB code.

To answer your question, I guess this would be a one time update.

Thanks

 

[Guest]

I just pasted one of you values into a field in a table
999/999-9999
I opened the table in datasheet view and placed my cursor in the field. I
selected Edit->Replace and entered / to find and didn't enter anything in the
Replace With box. I selected Match Any Part of Field and clicked Replace
All. My results were exactly what I expected.

 

[Guest]

Thank you. This is an excellent reply. However, I am going to need some
extra help here. First of all, my Access training does not yet include
Modules--meaning I am not too familiar with them and need greater detailed
instruction here.

Second, I think the Update query would work best as I am constantly
collecting data and adding to the larger database now totaling 40,000 entries.

Can Telephone in your code be a substitute for Phone or fax? I am trying to
update both these fields.

Also, please tell me how to insert this code into the update query. Please
forgive my novicity here. Thanks.

You can use a VBA function to do this. Paste the function below into a
module and save it the module (Don't name the module the same as the
function)

If you want to do this permanently then use the function in an Update query.
If you just want to do this to see the value then you can use the function
in the select clause

SELECT fStripToNumbersOnly([Telephone]) as PhoneNum
FROM YourTable

IF you have a lot of records this could be slow.

The update query would look like
UPDATE YourTable
SET [Telephone] = fStripToNumbersOnly([YourTable].[Telephone])
WHERE [Telephone] is Not Null


Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As Variant
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = varText
Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

A project that I am doing requires collecting large amounts of contact data
and working with the phone numbers. My job would be a lot easier if the
formatting on the phone numbers were deleted. For example, the left and
right parentheses, Slashes, dashes and spaces cause some problems and I
would
like to know how to remove them. I am not knowledgable in writing code
but
do know a few things about queries. Everything I try results in a syntax
error message. What can you suggest?

 

[Guest]

Perusing your response further, the line

Const strNumbers As String = "0123456789"

is exactly what I want for the Phone and Fax fields. Now please tell me how
to insert your code into an Update query. Thanks.

You can use a VBA function to do this. Paste the function below into a
module and save it the module (Don't name the module the same as the
function)

If you want to do this permanently then use the function in an Update query.
If you just want to do this to see the value then you can use the function
in the select clause

SELECT fStripToNumbersOnly([Telephone]) as PhoneNum
FROM YourTable

IF you have a lot of records this could be slow.

The update query would look like
UPDATE YourTable
SET [Telephone] = fStripToNumbersOnly([YourTable].[Telephone])
WHERE [Telephone] is Not Null


Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As Variant
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = varText
Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

A project that I am doing requires collecting large amounts of contact data
and working with the phone numbers. My job would be a lot easier if the
formatting on the phone numbers were deleted. For example, the left and
right parentheses, Slashes, dashes and spaces cause some problems and I
would
like to know how to remove them. I am not knowledgable in writing code
but
do know a few things about queries. Everything I try results in a syntax
error message. What can you suggest?

 

[John Spencer]

OK.

Create the module with the code first.

Open a new module in the database
-- Open database in design mode
-- Click on Modules
-- Click on New button at the top
-- Paste in the posted code
-- Select File: Save ... from the menu and accept the proposed name module#

MAKE A BACKUP of your data
Now that the module is built, go back to the database window
-- Create a new query with your table and the field to be updated
-- Select Query: Update Query from the menu
-- Under the phone field
------ Update To: fStripToNumbersOnly ([YourTableName].[YourFieldName])
------ Criteria: Not Like "##########"
The criteria will screen out records that are already in the format of 10
numeric characters. Since you may be doing this over and over it makes
sense to avoid processing records that already meet the format you want.

I would set up a separate query to handle the Fax numbers. You can do both
the fax and telephone numbers in one query if you wish.

AS I said backup up your data first (at least the until you are sure this
works for you).

By the way an alternative is to add two columns to your table FixedPhone and
FixedFax and UPDATE those fields to the calculated values of the existing
fields. That way you will have both the input version and the calculated
version of the numbers to compare.

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Perusing your response further, the line

Const strNumbers As String = "0123456789"

is exactly what I want for the Phone and Fax fields. Now please tell me
how
to insert your code into an Update query. Thanks.

You can use a VBA function to do this. Paste the function below into a
module and save it the module (Don't name the module the same as the
function)

If you want to do this permanently then use the function in an Update
query.
If you just want to do this to see the value then you can use the
function
in the select clause

SELECT fStripToNumbersOnly([Telephone]) as PhoneNum
FROM YourTable

IF you have a lot of records this could be slow.

The update query would look like
UPDATE YourTable
SET [Telephone] = fStripToNumbersOnly([YourTable].[Telephone])
WHERE [Telephone] is Not Null


Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As Variant
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = varText
Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

A project that I am doing requires collecting large amounts of contact
data
and working with the phone numbers. My job would be a lot easier if
the
formatting on the phone numbers were deleted. For example, the left
and
right parentheses, Slashes, dashes and spaces cause some problems and I
would
like to know how to remove them. I am not knowledgable in writing code
but
do know a few things about queries. Everything I try results in a
syntax
error message. What can you suggest?

 

[Michael Gramelspacher]

(e-mail address removed)>, (e-mail address removed)
says...

Perusing your response further, the line

Const strNumbers As String = "0123456789"

is exactly what I want for the Phone and Fax fields. Now please tell me how
to insert your code into an Update query. Thanks.

You can use a VBA function to do this. Paste the function below into a
module and save it the module (Don't name the module the same as the
function)

If you want to do this permanently then use the function in an Update query.
If you just want to do this to see the value then you can use the function
in the select clause

SELECT fStripToNumbersOnly([Telephone]) as PhoneNum
FROM YourTable

IF you have a lot of records this could be slow.

The update query would look like
UPDATE YourTable
SET [Telephone] = fStripToNumbersOnly([YourTable].[Telephone])
WHERE [Telephone] is Not Null


Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As Variant
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = varText
Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function

Something else to try. Use your table and column names.

UPDATE Table1 SET Table1.Phone_nbr = Replace(Replace(Replace
(Replace(Replace(Replace([phone_nbr],"-",""),"/",""),"
",""),".",""),")",""),"(","");

 

[Guest]

Duane, I tried this the way you suggested and it worked for a few of the
entries, but not all. For example, I entered the Left Parenthesis ( and
clicked Replace all, Any Part of Field, and it removed it from a portion of
the entries but not all. Same thing with Right Parenthesis and the dash.

Also, how do I remove the unnecessary spaces? Thank you.

 

[Guest]

Thanks again for this response. Let's continue to work together until we get
it right. I have done what you suggested as far as creating the module.
When I try to save the module I get the following error, "Compile Error.
Invalid Outside Procedure." The the very first If statement has "varText"
highlighted.
When I attempt to do the Update Query where I copy and past your code into
the Update to field, I get the error message "Expression you entered contains
invalid syntax." WHERE is highlighted. Then I insert my table name and Fax
for Telephone. Please help.

OK.

Create the module with the code first.

Open a new module in the database
-- Open database in design mode
-- Click on Modules
-- Click on New button at the top
-- Paste in the posted code
-- Select File: Save ... from the menu and accept the proposed name module#

MAKE A BACKUP of your data
Now that the module is built, go back to the database window
-- Create a new query with your table and the field to be updated
-- Select Query: Update Query from the menu
-- Under the phone field
------ Update To: fStripToNumbersOnly ([YourTableName].[YourFieldName])
------ Criteria: Not Like "##########"
The criteria will screen out records that are already in the format of 10
numeric characters. Since you may be doing this over and over it makes
sense to avoid processing records that already meet the format you want.

I would set up a separate query to handle the Fax numbers. You can do both
the fax and telephone numbers in one query if you wish.

AS I said backup up your data first (at least the until you are sure this
works for you).

By the way an alternative is to add two columns to your table FixedPhone and
FixedFax and UPDATE those fields to the calculated values of the existing
fields. That way you will have both the input version and the calculated
version of the numbers to compare.

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Perusing your response further, the line

Const strNumbers As String = "0123456789"

is exactly what I want for the Phone and Fax fields. Now please tell me
how
to insert your code into an Update query. Thanks.

You can use a VBA function to do this. Paste the function below into a
module and save it the module (Don't name the module the same as the
function)

If you want to do this permanently then use the function in an Update
query.
If you just want to do this to see the value then you can use the
function
in the select clause

SELECT fStripToNumbersOnly([Telephone]) as PhoneNum
FROM YourTable

IF you have a lot of records this could be slow.

The update query would look like
UPDATE YourTable
SET [Telephone] = fStripToNumbersOnly([YourTable].[Telephone])
WHERE [Telephone] is Not Null


Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As Variant
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = varText
Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

A project that I am doing requires collecting large amounts of contact
data
and working with the phone numbers. My job would be a lot easier if
the
formatting on the phone numbers were deleted. For example, the left
and
right parentheses, Slashes, dashes and spaces cause some problems and I
would
like to know how to remove them. I am not knowledgable in writing code
but
do know a few things about queries. Everything I try results in a
syntax
error message. What can you suggest?

 

[Guest]

I attempted this but received error messages regarding Invalid Syntax errors.
The parentheses were highlighted. I did substitute my table name and "Fax",
the name of the field this is for where Phone_nbr is placed.

(e-mail address removed)>, (e-mail address removed)
says...

Perusing your response further, the line

Const strNumbers As String = "0123456789"

is exactly what I want for the Phone and Fax fields. Now please tell me how
to insert your code into an Update query. Thanks.

You can use a VBA function to do this. Paste the function below into a
module and save it the module (Don't name the module the same as the
function)

If you want to do this permanently then use the function in an Update query.
If you just want to do this to see the value then you can use the function
in the select clause

SELECT fStripToNumbersOnly([Telephone]) as PhoneNum
FROM YourTable

IF you have a lot of records this could be slow.

The update query would look like
UPDATE YourTable
SET [Telephone] = fStripToNumbersOnly([YourTable].[Telephone])
WHERE [Telephone] is Not Null


Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As Variant
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = varText
Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function

Something else to try. Use your table and column names.

UPDATE Table1 SET Table1.Phone_nbr = Replace(Replace(Replace
(Replace(Replace(Replace([phone_nbr],"-",""),"/",""),"
",""),".",""),")",""),"(","");

 

[Michael Gramelspacher]

(e-mail address removed)>, (e-mail address removed)
says...

I attempted this but received error messages regarding Invalid Syntax errors.
The parentheses were highlighted. I did substitute my table name and "Fax",
the name of the field this is for where Phone_nbr is placed.

In my message what is a single line was wrapped into three
lines. Probably you did not get it back together correctly as
one line.

It did actually work in my database.

 

[Guest]

You should try copying a character you want to get rid of to the clipboard
and then use it in the Find What. You can replace a space with nothing.

I thought this would have taken about 1 minute to accomplish so I didn't
suggest any of the great code/function methods. I now think I would have
tried a code solution since it could be a good learning tool and is usable
again and again.

 

[John Spencer]

Did you post the entire function into the vba module? When you did so,
did any of the code lines change color? I would expect the error
message to occur if you had dropped the first line. The following is
the first line and it should all be on one line.


Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant

The code should look like

'---------- Code Starts -----------------

Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant
'Takes input and returns only the numbers in the input.
'Strips out all other characters.
'Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As Variant
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = varText
Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function
'------------ Code end ----------------

I am going out of town for a week - starting tomorrow-, so you may need
to start a new thread if this does not solve your problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks again for this response. Let's continue to work together until we get
it right. I have done what you suggested as far as creating the module.
When I try to save the module I get the following error, "Compile Error.
Invalid Outside Procedure." The the very first If statement has "varText"
highlighted.
When I attempt to do the Update Query where I copy and past your code into
the Update to field, I get the error message "Expression you entered contains
invalid syntax." WHERE is highlighted. Then I insert my table name and Fax
for Telephone. Please help.

OK.

Create the module with the code first.

Open a new module in the database
-- Open database in design mode
-- Click on Modules
-- Click on New button at the top
-- Paste in the posted code
-- Select File: Save ... from the menu and accept the proposed name module#

MAKE A BACKUP of your data
Now that the module is built, go back to the database window
-- Create a new query with your table and the field to be updated
-- Select Query: Update Query from the menu
-- Under the phone field
------ Update To: fStripToNumbersOnly ([YourTableName].[YourFieldName])
------ Criteria: Not Like "##########"
The criteria will screen out records that are already in the format of 10
numeric characters. Since you may be doing this over and over it makes
sense to avoid processing records that already meet the format you want.

I would set up a separate query to handle the Fax numbers. You can do both
the fax and telephone numbers in one query if you wish.

AS I said backup up your data first (at least the until you are sure this
works for you).

By the way an alternative is to add two columns to your table FixedPhone and
FixedFax and UPDATE those fields to the calculated values of the existing
fields. That way you will have both the input version and the calculated
version of the numbers to compare.

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Perusing your response further, the line

Const strNumbers As String = "0123456789"

is exactly what I want for the Phone and Fax fields. Now please tell me
how
to insert your code into an Update query. Thanks.
:

You can use a VBA function to do this. Paste the function below into a
module and save it the module (Don't name the module the same as the
function)

If you want to do this permanently then use the function in an Update
query.
If you just want to do this to see the value then you can use the
function
in the select clause

SELECT fStripToNumbersOnly([Telephone]) as PhoneNum
FROM YourTable

IF you have a lot of records this could be slow.

The update query would look like
UPDATE YourTable
SET [Telephone] = fStripToNumbersOnly([YourTable].[Telephone])
WHERE [Telephone] is Not Null


Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As Variant
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = varText
Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

A project that I am doing requires collecting large amounts of contact
data
and working with the phone numbers. My job would be a lot easier if
the
formatting on the phone numbers were deleted. For example, the left
and
right parentheses, Slashes, dashes and spaces cause some problems and I
would
like to know how to remove them. I am not knowledgable in writing code
but
do know a few things about queries. Everything I try results in a
syntax
error message. What can you suggest?

 

[Guest]

This works. Thank you very much for your help. I wanted to let you know it
is very much appreciated.

Did you post the entire function into the vba module? When you did so,
did any of the code lines change color? I would expect the error
message to occur if you had dropped the first line. The following is
the first line and it should all be on one line.


Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant

The code should look like

'---------- Code Starts -----------------

Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant
'Takes input and returns only the numbers in the input.
'Strips out all other characters.
'Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As Variant
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = varText
Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function
'------------ Code end ----------------

I am going out of town for a week - starting tomorrow-, so you may need
to start a new thread if this does not solve your problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks again for this response. Let's continue to work together until we get
it right. I have done what you suggested as far as creating the module.
When I try to save the module I get the following error, "Compile Error.
Invalid Outside Procedure." The the very first If statement has "varText"
highlighted.
When I attempt to do the Update Query where I copy and past your code into
the Update to field, I get the error message "Expression you entered contains
invalid syntax." WHERE is highlighted. Then I insert my table name and Fax
for Telephone. Please help.

OK.

Create the module with the code first.

Open a new module in the database
-- Open database in design mode
-- Click on Modules
-- Click on New button at the top
-- Paste in the posted code
-- Select File: Save ... from the menu and accept the proposed name module#

MAKE A BACKUP of your data
Now that the module is built, go back to the database window
-- Create a new query with your table and the field to be updated
-- Select Query: Update Query from the menu
-- Under the phone field
------ Update To: fStripToNumbersOnly ([YourTableName].[YourFieldName])
------ Criteria: Not Like "##########"
The criteria will screen out records that are already in the format of 10
numeric characters. Since you may be doing this over and over it makes
sense to avoid processing records that already meet the format you want.

I would set up a separate query to handle the Fax numbers. You can do both
the fax and telephone numbers in one query if you wish.

AS I said backup up your data first (at least the until you are sure this
works for you).

By the way an alternative is to add two columns to your table FixedPhone and
FixedFax and UPDATE those fields to the calculated values of the existing
fields. That way you will have both the input version and the calculated
version of the numbers to compare.

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Perusing your response further, the line

Const strNumbers As String = "0123456789"

is exactly what I want for the Phone and Fax fields. Now please tell me
how
to insert your code into an Update query. Thanks.
:

You can use a VBA function to do this. Paste the function below into a
module and save it the module (Don't name the module the same as the
function)

If you want to do this permanently then use the function in an Update
query.
If you just want to do this to see the value then you can use the
function
in the select clause

SELECT fStripToNumbersOnly([Telephone]) as PhoneNum
FROM YourTable

IF you have a lot of records this could be slow.

The update query would look like
UPDATE YourTable
SET [Telephone] = fStripToNumbersOnly([YourTable].[Telephone])
WHERE [Telephone] is Not Null


Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As Variant
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = varText
Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

A project that I am doing requires collecting large amounts of contact
data
and working with the phone numbers. My job would be a lot easier if
the
formatting on the phone numbers were deleted. For example, the left
and
right parentheses, Slashes, dashes and spaces cause some problems and I
would
like to know how to remove them. I am not knowledgable in writing code
but
do know a few things about queries. Everything I try results in a
syntax
error message. What can you suggest?

 

[Guest]

John, sorry to bother you with this again, but I've run into a problem.
Twice now, in applying the code to two other databases, when the query has
been run, it wiped out all my fax nos. I used this to reformat the fax field.
What it did was wipe out the entire fax number except for the 1 at the
beginning. The 1 was inserted by means of an update query to facilitate the
fax program. When I ran the query you suggested--
Update To: fStripToNumbersOnly ([YourTableName].[YourFieldName])
------ Criteria: Not Like "##########"

all the fax nos were wiped out and only the 1 at the beginning was left in
each of the records.

Please tell me what might have happened, what I did wrong and what may be
done to correct it.

Thanks.

You can use a VBA function to do this. Paste the function below into a
module and save it the module (Don't name the module the same as the
function)

If you want to do this permanently then use the function in an Update query.
If you just want to do this to see the value then you can use the function
in the select clause

SELECT fStripToNumbersOnly([Telephone]) as PhoneNum
FROM YourTable

IF you have a lot of records this could be slow.

The update query would look like
UPDATE YourTable
SET [Telephone] = fStripToNumbersOnly([YourTable].[Telephone])
WHERE [Telephone] is Not Null


Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As Variant
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = varText
Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

A project that I am doing requires collecting large amounts of contact data
and working with the phone numbers. My job would be a lot easier if the
formatting on the phone numbers were deleted. For example, the left and
right parentheses, Slashes, dashes and spaces cause some problems and I
would
like to know how to remove them. I am not knowledgable in writing code
but
do know a few things about queries. Everything I try results in a syntax
error message. What can you suggest?