Removing Formatting Symbols from Phone numbers

[John Spencer]

Dumb question, but I've got to ask.

Did you replace YourTableName and YourFieldName with the names of your table
and Fax field?

Does Access add quote marks around the table and field names or around the
entire expression? There should be NO quote marks. If there are, the
function will not get the value from the field, but will process the string
contained between the quote marks.

Update To: fStripToNumbersOnly("YourTable.YourField") is bad as there are
no numbers in the phrase "YourTable.YourField"

IF you can't solve this please switch to SQL view and post the SQL text of
the query that is failing.

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

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?

 

[Guest]

Yes, there are quote marks. What gets me is that with some tables, I insert
the table name and fax in the appropriate places and they work fine, others
don't. Here is the SQL view.

UPDATE LG9LongDistanceTABLE SET LG9LongDistanceTABLE.Fax =
fStripToNumbersOnly("[LG9LongDistanceTABLE].[Fax]")
WHERE ((([LG9LongDistanceTABLE].[Fax]) Not Like "##########"));


Thanks.

Dumb question, but I've got to ask.

Did you replace YourTableName and YourFieldName with the names of your table
and Fax field?

Does Access add quote marks around the table and field names or around the
entire expression? There should be NO quote marks. If there are, the
function will not get the value from the field, but will process the string
contained between the quote marks.

Update To: fStripToNumbersOnly("YourTable.YourField") is bad as there are
no numbers in the phrase "YourTable.YourField"

IF you can't solve this please switch to SQL view and post the SQL text of
the query that is failing.

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

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?

 

[John Spencer]

THERE SHOULD NOT BE QUOTE MARKS. The query should look like the following

UPDATE LG9LongDistanceTABLE
SET LG9LongDistanceTABLE.Fax =
fStripToNumbersOnly([LG9LongDistanceTABLE].[Fax])
WHERE ((([LG9LongDistanceTABLE].[Fax]) Not Like "##########"));

Sometimes the Design View (query grid) will insert quotes in the update to
when you do not want them. This seems to be what happened here.

You should be able to remove the quotes in the query grid and this should
run correctly. The usual warning applies, back up your data first and then
run the query. Once you are sure it is working, then you can skip backing
up just before running the query.

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

Yes, there are quote marks. What gets me is that with some tables, I
insert
the table name and fax in the appropriate places and they work fine,
others
don't. Here is the SQL view.

UPDATE LG9LongDistanceTABLE SET LG9LongDistanceTABLE.Fax =
fStripToNumbersOnly("[LG9LongDistanceTABLE].[Fax]")
WHERE ((([LG9LongDistanceTABLE].[Fax]) Not Like "##########"));


Thanks.

Dumb question, but I've got to ask.

Did you replace YourTableName and YourFieldName with the names of your
table
and Fax field?

Does Access add quote marks around the table and field names or around
the
entire expression? There should be NO quote marks. If there are, the
function will not get the value from the field, but will process the
string
contained between the quote marks.

Update To: fStripToNumbersOnly("YourTable.YourField") is bad as there
are
no numbers in the phrase "YourTable.YourField"

IF you can't solve this please switch to SQL view and post the SQL text
of
the query that is failing.

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

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?

 

[Guest]

I think this just about gets it. Thanks for all your help. It is most
appreciated.

THERE SHOULD NOT BE QUOTE MARKS. The query should look like the following

UPDATE LG9LongDistanceTABLE
SET LG9LongDistanceTABLE.Fax =
fStripToNumbersOnly([LG9LongDistanceTABLE].[Fax])
WHERE ((([LG9LongDistanceTABLE].[Fax]) Not Like "##########"));

Sometimes the Design View (query grid) will insert quotes in the update to
when you do not want them. This seems to be what happened here.

You should be able to remove the quotes in the query grid and this should
run correctly. The usual warning applies, back up your data first and then
run the query. Once you are sure it is working, then you can skip backing
up just before running the query.

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

Yes, there are quote marks. What gets me is that with some tables, I
insert
the table name and fax in the appropriate places and they work fine,
others
don't. Here is the SQL view.

UPDATE LG9LongDistanceTABLE SET LG9LongDistanceTABLE.Fax =
fStripToNumbersOnly("[LG9LongDistanceTABLE].[Fax]")
WHERE ((([LG9LongDistanceTABLE].[Fax]) Not Like "##########"));


Thanks.

Dumb question, but I've got to ask.

Did you replace YourTableName and YourFieldName with the names of your
table
and Fax field?

Does Access add quote marks around the table and field names or around
the
entire expression? There should be NO quote marks. If there are, the
function will not get the value from the field, but will process the
string
contained between the quote marks.

Update To: fStripToNumbersOnly("YourTable.YourField") is bad as there
are
no numbers in the phrase "YourTable.YourField"

IF you can't solve this please switch to SQL view and post the SQL text
of
the query that is failing.

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

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?