Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Auto update to add characters?

Results 1 to 9 of 9
Share This Thread →
  1. #1
    mdmaro is offline Registered User
    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
    sync_or_swim's Avatar
    sync_or_swim is offline Contributing User
    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

  3. #3
    don94403's Avatar
    don94403 is offline Contributing User
    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.

  4. #4
    mdmaro is offline Registered User
    Join Date
    Mar 2009
    Posts
    7
    Rep Power
    0
    Quote 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 !!

  5. #5
    sync_or_swim's Avatar
    sync_or_swim is offline Contributing User
    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

  6. #6
    mdmaro is offline Registered User
    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?

  7. #7
    don94403's Avatar
    don94403 is offline Contributing User
    Join Date
    Jan 2007
    Location
    Northern California
    Posts
    3,536
    Rep Power
    763
    Quote 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.

  8. #8
    mdmaro is offline Registered User
    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);

  9. #9
    don94403's Avatar
    don94403 is offline Contributing User
    Join Date
    Jan 2007
    Location
    Northern California
    Posts
    3,536
    Rep Power
    763
    Quote 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 06:31 PM.

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

Similar Threads

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

ASP Free Advertisers and Affiliates