#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2009
    Posts
    7
    Rep Power
    0

    Auto update to add characters?


    I have a field (in a table with over 300,000 records) that includes a social security number (#########) but I need to find a way to change it to add dashes (###-##-####) and was wondering if there was a way I can do it all at once. Any help?
  2. #2
  3. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Mar 2006
    Location
    South Wales
    Posts
    3,674
    Rep Power
    2043
    Hi,

    One suggestion would be that instead of overwriting your data you could just apply the formatting as you select the data, eg:
    Code:
    SELECT LEFT(ssn, 3) + '-' +  MID(ssn, 3, 2) + '-' + RIGHT(ssn, 4) AS ssn FROM yourtable
  4. #3
  5. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jan 2007
    Location
    Northern California
    Posts
    3,536
    Rep Power
    763
    Is the ssn field in your Table a Text or Number data type? If it is Text, sync_or_swim's suggestion is the best way to go.
    Experience is the thing you have left when everything else is gone.
  6. #4
  7. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2009
    Posts
    7
    Rep Power
    0
    Originally Posted by don94403
    Is the ssn field in your Table a Text or Number data type? If it is Text, sync_or_swim's suggestion is the best way to go.
    Yes it is a text field. I was actually doing it so I could match the SSN field with another table that already had the dashes in it. I believe it worked even though I ended up having no where near as many matches as I thought I would.

    Thank you !!
  8. #5
  9. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Mar 2006
    Location
    South Wales
    Posts
    3,674
    Rep Power
    2043
    Glad you got it sorted!! Just as a double-check you could try removing the dashes from your other table which contains hashes, something like (not tested):
    Code:
    select count(*) from table1 t1, table2 t2 where REPLACE(t1.ssn, '-', '') = t2.ssn
  10. #6
  11. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2009
    Posts
    7
    Rep Power
    0
    Alright, still having this problem. Instead of

    Code:
    SELECT LEFT(ssn, 3) + '-' +  MID(ssn, 3, 2) + '-' + RIGHT(ssn, 4) AS ssn FROM yourtable
    which just temporarily adds the dashes in the middle of a query, I need to permanently update the field to include the dashes.

    Any help?
  12. #7
  13. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jan 2007
    Location
    Northern California
    Posts
    3,536
    Rep Power
    763
    Originally Posted by mdmaro
    Alright, still having this problem. Instead of

    Code:
    SELECT LEFT(ssn, 3) + '-' +  MID(ssn, 3, 2) + '-' + RIGHT(ssn, 4) AS ssn FROM yourtable
    which just temporarily adds the dashes in the middle of a query, I need to permanently update the field to include the dashes.

    Any help?
    You could run an Update Query, updating the SSN column with a function like this, in a Module:
    Code:
    Public Function ssnnormal(ssn As String)
        Dim tempssn As String
        tempssn = Replace(ssn, "-", "")
        If Len(tempssn) = 9 Then
            tempssn = Left(tempssn, 3) & "-" & Mid(tempssn, 4, 2) & "-" & Mid(tempssn, 6)
        Else
            tempssn = ssn
        End If
        ssnnormal = tempssn
    End Function
    In the "Update To" row of your Update Query, in the SSN column, you would enter:
    Code:
    ssnnormal([SSN])
    (assuming that your column name is "SSN")

    What that does is, first test if there are exactly nine characters in the data after removing any hyphens; if not, just leave it as is. If there are exactly 9 characters, insert hyphens after the 3rd and 5th characters. Expect to receive the error box that says "can't update all the records in the update query" if you have some records with Null SSN's, but don't worry, all the others will be updated.

    That said, ANY time you're making a massive change of data like this, ALWAYS save an unchanged copy of the table (or of the entire database) until you've made the change and verified that you haven't lost any data.
  14. #8
  15. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2009
    Posts
    7
    Rep Power
    0
    where in access would I put that? I'm using access 2007 and using the sql knowledge i have in design view when creating a query. I'm not sure where i would put code like that.

    also, does this look like something that would work?

    Code:
    UPDATE Table
    SET SSN = LEFT(ssn,3)+'-'+MID(ssn,3,2)+'-'+RIGHT(ssn,4);
  16. #9
  17. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jan 2007
    Location
    Northern California
    Posts
    3,536
    Rep Power
    763
    Originally Posted by mdmaro
    where in access would I put that? I'm using access 2007 and using the sql knowledge i have in design view when creating a query. I'm not sure where i would put code like that.

    also, does this look like something that would work?

    Code:
    UPDATE Table
    SET SSN = LEFT(ssn,3)+'-'+MID(ssn,3,2)+'-'+RIGHT(ssn,4);
    That would work in theory, but with 300,000 records, I can pretty well guarantee you that you will have records where the ssn is not formatted correctly. That's why I wrote the function to first insure that there are exactly 9 significant digits, with or without hyphens, before formatting a value.

    In the Database Window (I'm sure this hasn't changed that much in 2007), there is a tab for Modules. You will first have to open a new module; it doesn't matter what you name it, "Module1" works. When you open that module, you will probably see something like "Option Explicit" at the top. That's fine, just leave that alone. Under that, paste the code I showed in my previous post, then save your new module and close the VBA editor window. You can then use that function anywhere in that Access database. In this case, you would use it in the Update To row of your Update Query.

    If you'd like to first get a feeling for how such a function works, it's very easy to test it without affecting your database at all. After you have saved your new function, press Ctrl+G to open the "immediate" window, where you can enter command line VBA directly. It's a small window at the bottom of the VBA editor window (if it's the same in 2007). To test your new function, enter the following in the immediate window (the ? is just a shortcut for saying "print the following"):
    Code:
     ? ssnnormal("123456789")
    and when you press Enter, it should print "123-45-6789" right below. You can try other numbers, with or without hyphens, but if you input anything with a different number of significant characters (excluding any hyphens), it will just return exactly what was input, unchanged.

    Another concept for you to consider, especially with a large database such as yours, is that you might want to add a new column to your table (say, call it "ssn_hyphens") and instead of updating the "ssn" column, leave that alone and populate the new "ssn_hyphens" column, so you would have both in the table.
    Last edited by don94403; April 22nd, 2009 at 07:31 PM.

Similar Threads

  1. Problem writing to database
    By twave in forum ASP Development
    Replies: 25
    Last Post: December 20th, 2006, 11:22 AM
  2. Auto Update Form
    By James D Ballott in forum Microsoft Access Help
    Replies: 0
    Last Post: November 15th, 2006, 09:59 PM
  3. Problem to update ntext field with latin characters
    By buzyonok in forum SQL Development
    Replies: 1
    Last Post: November 3rd, 2005, 04:59 AM
  4. MHTMLRedir.Exploit - Revisited
    By Tolik in forum Windows OS
    Replies: 8
    Last Post: August 9th, 2005, 06:18 AM
  5. UPDATE: MySQL Auto Backup & Export v1.1
    By Beyonder in forum SQL Development
    Replies: 0
    Last Post: March 20th, 2005, 09:21 PM

IMN logo majestic logo threadwatch logo seochat tools logo