Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting Hyphens from a Field (Access 2003)

    I have received 600,000+ data records that I need to clean and make compatible with three million others. One field has an account number with hyphens. The field in the other data is a number. Unfortunately, there are not the same number of numbers between the hyphens, which would make removing them with an update query relatively easy.

    I know there's a way to remove the hyphens - code would be fine - but I'm drawing a blank this afternoon.

    Any suggestions will be appreciated.

    Thanks for your help.

    Nancy

  2. #2
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Hyphens from a Field (Access 2003)

    Hi Nancy,
    If all you need to do is to remove the hyphens and then "collapse" the data; i.e. 123-45-6789 => 123456789; what I do is to just open the table, highlight the column in question and use the Replace function under Edit. See attached picture.
    Place you hyphen in the Find What.....place nothing in Replace With......Look In is your field name.....and select Any Part of Field for Match.
    I'd make a copy of the table and try it there first. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  3. #3
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Hyphens from a Field (Access 2003)

    Duh...

    I knew I was making it more difficult than it needed to be! Thanks for your help.

    Nancy

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting Hyphens from a Field (Access 2003)

    Hi Nancy

    I am always worried about deleting characters from tables and will only do this after I have made a back up. On the other hand i have written a little function that may help you in the future.

    Create a module and paste in the following code:

    <div style="width: 100%; background-color: #FFFFFF;">Function ReplaceChar(Target As String, SearchChar, ReplaceChar) As String

    Dim i As Integer
    Dim tempstring As String

    For i = 1 To Len(Target)
    If Mid(Target, i, 1) = SearchChar Then
    tempstring = tempstring & ReplaceChar
    Else
    tempstring = tempstring & Mid(Target, i, 1)
    End If
    Next i
    ReplaceCharacter = tempstring
    End Function</div hiblock>

    Create a query from the table and then add a new field in the query grid and type this NewString:ReplaceChar([MyField],"-","") where MyField is the field with the hyphen in it.


    This function is quite good as it can get rid of multiple occurences of a hyphen or other character in a string.

    Once you have run it, make a Make Table query and exclude the old field which has a hyphen in it. You now have a clean set of data and a back up....enjoy
    Jerry

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Deleting Hyphens from a Field (Access 2003)

    Jerry,
    Why not just use the built-in Replace function? E.g.:
    <code>MyNewField: Replace([MyField],"findstring","replacestring")</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Hyphens from a Field (Access 2003)

    Thanks, Rory. Worked a lot fast than Find | Replace.

    Nancy

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting Hyphens from a Field (Access 2003)

    I have this in my reusable code folder. The reason I use it is because I often get recordsets sent to me from legacy systems or more modern systems. I have had experiences when some of the field data has multiple char(13) and char(10) characters in the string. The method you provide has given me no problems in the past when I have wanted to remove, say, a single hyphen or any other single character but it has failed on multiple incidents of a character in the string.

    For my troublesome datasets I use this code as it is a surefire way to lose out the 'nasties'.

    So I would say it was different horses for different courses, both methods work but I stay with my reusable stuff as I have tested it and I know it will work without having to scroll through.
    Jerry

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Deleting Hyphens from a Field (Access 2003)

    Jerry,
    I have never had a problem with that function replacing multiple instances of a character. Additionally, it will work to replace more than one character. Do you have the same problem if you use the VBA Replace function in code instead of iterating through each character?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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