Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53

    Talking Unanswered: A little Guidance

    Ok I have two tables each with SS# in each table. They are both Text fields in each table, however when I try to do a join on them It will not pull based on the SS# and I believe that it's because one has the - inbetween the numbers and the other one has an input mask defined at the table level. How do I get the two tables to be unified in the SS#? This way I can query agains the two with a join that might work. Looking for an easy way to do this. Any help would be helpful.

    Thanks,
    CAT :-D

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by carley465
    . How do I get the two tables to be unified in the SS#?

    You have to chose one common format. You get either xxx-xx-xxxx or xxxxxxxxx. All of your keys should have a common format in general. That's as much for readability as technical concerns. Basically you are creating unnecessary inconsistency.

    I would probably remove the dashes from the social security number. Actually, I would use an arbitrary key. Store the social security number as a field associated with a given person, then give that person an arbitrary id to use in the rest of the system. There are two reasons to do that. Most people agree that primary keys should have NO intrinsic value to the record itself. Second, in this situation you have privacy and security issues.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53
    Quote Originally Posted by Teddy
    You have to chose one common format. You get either xxx-xx-xxxx or xxxxxxxxx. All of your keys should have a common format in general. That's as much for readability as technical concerns. Basically you are creating unnecessary inconsistency.

    I would probably remove the dashes from the social security number. Actually, I would use an arbitrary key. Store the social security number as a field associated with a given person, then give that person an arbitrary id to use in the rest of the system. There are two reasons to do that. Most people agree that primary keys should have NO intrinsic value to the record itself. Second, in this situation you have privacy and security issues.


    I understand I have to choose one common format, but is there and easy way to either insert the dashes or remove them? Time is key right now in this.

    Thanks
    CAT :-D

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    removing them is easier. Look into the Mid() function in the access help file. Alternately you can use find and replace in datasheet view.
    Last edited by Teddy; 01-06-05 at 11:40.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    ... And if you make a query to do it then it'll be done ... (Yogi - AFLAC )

    UPDATE SomeTable SET MySSN=REPLACE(MySSN,'-','');
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is there replace() in higher versions of Access? I reflexivly suggested the Replace() function only to discover it doesn't exist in 97 (too much T-SQL does that too you).
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I think it's in Access 2000 and up ... I know it's there in 2002. Well if it's not there for this poster, they can run a recordset to do the same thing by updating by using the format function ...
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yup
    there's a replace in this copy of Access 2002
    Is it possible you are missing a library or reference, I thought replace was a standard VB / VBA function.
    HTH

  9. #9
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53
    yes there is a Replace() in the higer Versions of Access and it worked like a charm. Thank you so much. This was such a great help. Now I can stop hitting my head against the cement wall.

    Quote Originally Posted by Teddy
    Is there replace() in higher versions of Access? I reflexivly suggested the Replace() function only to discover it doesn't exist in 97 (too much T-SQL does that too you).
    CAT :-D

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by healdem
    yup
    there's a replace in this copy of Access 2002
    Is it possible you are missing a library or reference, I thought replace was a standard VB / VBA function.
    HTH
    Me too. I think it's 2k+. I used it all the time in my last project, now I'm in 97.

    Anywho, if the OP is running '97, you have to use mid() a couple times. I'm going to have to write a replace function for '97 now. Damn.
    Last edited by Teddy; 01-07-05 at 13:51.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Teddy
    I'm going to have to write a replace function for '97 now. Damn.
    And here it is...

    Code:
    Function replace(strIn As String, strFind As String, strReplace As String) As String
    Dim i As Integer
    Dim currPos As Integer
    Dim strOut As String
    
    strOut = strIn
    currPos = 1
    For i = 1 To Len(strIn)
        If Mid(strIn, currPos, Len(strFind)) = strFind Then
            strOut = Left(strOut, currPos - 1) & strReplace & Right(strOut, (Len(strOut) - currPos - Len(strFind) + 1))
            currPos = currPos + Len(strReplace)
        Else
            currPos = currPos + 1
        End If
    Next i
    
    replace = strOut
    
    End Function
    Anyone have a cleaner way to do this?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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