Reduce Phone field (Access 2000 SR1)

Topic

New Reply

I have inherited a database which contains phone numbers with and without the hyphen. This has resulted in duplicate phone numbers and mistakes. The field in the table is currently set to record numbers without the hyphen. How can I reduce numbers with the hyphen to a number without or vice versa? Is there an advantage to one or the other?

Reply | Quote

Replies

Firstly, I believe that there is an advantage in not recording the hyphen. We can use input masks or format properties to control how the number are displayed when they do not have the hypehn, but if they cotnain spaces and hypehns, then we are more limited in what we can do (or at least it becomes more difficult).

So..my suggestion would be to record only the phone numbers themselves, no hyphens and no spaces.

Okay, so how can you remove all the hyphens and spaces from a field in a table? Easy. Use the function that I have supplied below (and look how it is used in the example file attached).

To use the below function, place it in a new module in the database with the data you want to modify. Make sure you take a backup (ie. copy it and call it something different) of the data that you are going to be editing, just in case something goes wrong.

You need to pass three variables to the function, all strings

The first variable is the character that you wish to remove (ie. “-“, or ” “)
The second variable is the name of the table (ie. “tblPerson”)
The third variable is the name of the field that you want to search through (ie. “PhoneNumber”)

So calling the function would look something like this:

RemoveCharacter(“-“, “tblPerson”, “PhoneNumber”)

This would remove all hyphens from the PhoneNumber field in the table tblPerson.

Note that I haven’t included any error handling routines in this function, if you spell the table name or field name wrong. Just make sure you spell them right, or a funny error may occur (something like object not found in collection).

Hope that this helps

Public Function RemoveCharacter(strRemovalCharacter As String, strTableName As String, strFieldName As String)
‘===============================================================’
‘ Author: Jayden MacRae ‘
‘ Created: 27/10/2001 ‘
‘ Purpose: To remove any characters from a field in a table ‘
‘===============================================================’
Dim rstPerson As Recordset
Dim intHyphenPos As Integer
Dim strFieldValue As String

Set rstPerson = CurrentDb.OpenRecordset(strTableName, dbOpenTable)
With rstPerson
Do Until .EOF
Do
strFieldValue = .Fields(strFieldName)
intHyphenPos = InStr(1, strFieldValue, strRemovalCharacter)
If intHyphenPos > 0 Then
strFieldValue = Left(strFieldValue, intHyphenPos – 1) & _
Right(strFieldValue, Len(strFieldValue) – intHyphenPos)
End If
If strFieldValue .Fields(strFieldName) Then
.Edit
.Fields(strFieldName) = strFieldValue
.Update
End If
Loop Until intHyphenPos = 0
.MoveNext
Loop
.Close
End With

Set rstPerson = Nothing
End Function

Reply | Quote

I’m getting a compile error on .edit “Method not found”. I do have DAO 3.51, Access 9, ActiveX objects 2.1, visual basic and OLE Automation references loaded.

Reply | Quote

You need to turn off (unreference) your Active X 2.1

Reply | Quote

DAO 3.51 is the wrong version for Access 2000. A2k uses DAO 3.6. If you’re going to work with both object models (DAO and ADO), or even if you think you might, get accustomed to disambiguating your references on all model-specific objects. In other words, declare your objects as DAO.Database and DAO.Recordset to avoid confusing yourself and Access later on down the road. There are objects with the same name (like Recordset) in both models, and you can get some pretty odd compile and runtime errors if Access guesses at the wrong object model.

Reply | Quote

WOW! What a great function! I’m impressed. I did add an Nz to stringfieldvalue to handle null phone fields i.e. strFieldValue = Nz(.Fields(strFieldName)). Is there a better way to handle this or will this do? Many thanks. I am sure I will use this function many times in other databases.

Reply | Quote

I’m glad that I could help.

I would say that using the Nz() is the best way to handle it.

Cheers

Jayden

Reply | Quote

I’ve been watching your challenges with phone numbers with some interest. We recently made a massive structural change to a database with about 300,000 phone numbers in it, and we concluded we needed to store the hyphens in it. The real driving force was that we needed to store a significant number of international numbers (not North American dialing plan), and we needed a way to indicate things like the country code, as the people entering the numbers had no clue how it should be formated. What we did was preface all international numbers with the US direct dial international code 011- and then put a – after the country code, and if there was an area code for the country, a – between it and the local number. We also let them store extensions by putting an X after the main number.

We then built some code to validate phone numbers that checked for the correct number of digits and other such things, and we prompt them with the country name when they enter a country code. Most of this came about because we had about 5% garbage in the table before we did that. Now we have almost none. Hope this gives you some additional insight.

Reply | Quote

When I’ve had to deal with international numbers, I only used an input mask for US and Canadian numbers and stored the mask with the data. I accepted whatever they entered for international numbers because they were too hard (read “impossible”) to validate and format. Of course, we weren’t trying to use the numbers for dialing, which would make a difference.

Reply | Quote