Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Brandon, Manitoba, Canada
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reduce Phone field (Access 2000 SR1)

    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?

  2. #2
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reduce Phone field (Access 2000 SR1)

    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

    <font color=6495ed><font face="Georgia">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</font face=georgia></font color=6495ed>
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Brandon, Manitoba, Canada
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reduce Phone field (Access 2000 SR1)

    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.

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reduce Phone field (Access 2000 SR1)

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

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Brandon, Manitoba, Canada
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reduce Phone field (Access 2000 SR1)

    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.

  6. #6
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reduce Phone field (Access 2000 SR1)

    I'm glad that I could help. <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

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

    Cheers

    Jayden

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reduce Phone field (Access 2000 SR1)

    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.
    Charlotte

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Reduce Phone field (Access 2000 SR1)

    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.
    Wendell

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reduce Phone field (Access 2000 SR1)

    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.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •