Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369

    Unanswered: Can an uppercase character be located for Right function

    Is there any way to pull the second name off joined first and second names.

    BillAndrews------------->Andrews
    JohnSmith-------------->Smith
    AdamJones------------->Jones

    etc

    Thanks for any help

  2. #2
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    What you're looking for is a string tokenizer to allow you to go through the string letter by letter. You could then check each character to see if the Ascii value (Asc(x) where x is your letter) is between 65 and 90 (capital letters).

    I looked briefly and didn't see anything about a VB or MS Access tokenizer, so you may have to do something with String functions. Like this:

    Code:
    Public Function FindLastName(strName As String) As String
       Dim strTemp As String
       Dim i As Integer
       
       'start at 2 so we don't consider first capital
       For i = 2 To Len(strName)
    
          'take the letter in position i
          strTemp = Mid(strName, i, 1)
    
          If Asc(strTemp) < 90 And Asc(strTemp) > 65 Then 'then it's a capital
            FindLastName = Right$(strName, Len(strName) - i + 1)
          End If
       Next i
    End Function

    This doesnt' do any error checking or anything, so you may want to add that in. But if you try passing it a string like "BillAndrews" it will give you back "Andrews"

    Good luck. Hope it helps.


    Quote Originally Posted by Mike375
    Is there any way to pull the second name off joined first and second names.

    BillAndrews------------->Andrews
    JohnSmith-------------->Smith
    AdamJones------------->Jones

    etc

    Thanks for any help

  3. #3
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Many many thanks. It worked like a charm

    With over 5000 of them any alternative was not good

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I just realised that I forgot about the first name.

    I ran your your function as calulated query field which gave me the last name.

    How do I alter the function (a second function) to put first name in a calculated field.

    Thanks again.

    Mike

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I just noticed that the function is giving a Null when the last name starts with A

    JohnAndrews gives Null


    I had noticed a few blanks in the calculated field and when I sorted I saw it was all the lastnames starting with A

  6. #6
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Lastname starting with Z was also null.

    But I got them fixed

    Changed If Asc(strTemp) < 90 And Asc(strTemp) > 65 to

    If Asc(strTemp) < 91 And Asc(strTemp) > 64

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you're trying to clean up data in your tables... say you have a fullName field - instead of spacing them, split them into separate columns (firstName & lastName).

    It'll save all this pain
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    jmahaffie

    Just got the first name. The work around was starring me in the face. With your function I had the character count for the last name component.

    FN: Left([FS],[FSCount]-[CountLN])

    FS is the JohnAndrews and LN is last name courtesy of your function.

    Mid morning Friday in Australia, so I can now go to lunch

    Again, many thanks

    Mike

  9. #9
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    George

    That is what I have done via jmahaffie's function

    Actually, it is a large list of names (prospects) I received and the first and last name were joined.

  10. #10
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    jmahaffie

    The only other thing I need to do is cater for the

    McDonald etc and
    MacDonald etc

    BillMcDonald gets Donald as last name and BillMc as first name

    But I can do that OK with some IIFing etc

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'd be processing the string from left to right - using the Left() function to extract the firstname first... then the remainder of the string should be the last name. That copes with McDonald and also hyphenated surnames like JaneLomax-Smith etc.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I did try altering to Left but without success. Once I get way from Access inserting into Word bookmarks etc my coding is something less than poor

    I ended up doing the Mc and Mac by selecting them which was easy after first function had run.

    In fact it was crude I first selected the Mc records and joined those with function provided second name, that is, "Mc" & etc and ditto for Mac.

  13. #13
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    As an extension of all this is there a way to fix names such as

    Macnamara etc so as to capitalise the first letter after Mac or Mc

    I could do it with a crude work around because Mac and Mc give the number of characters so that would allow me to get namara etc into a calculated field and then convert to uppercase on first letter and then join with Mac or Mc.

  14. #14
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    I was waiting for someone to write in with something like StarTrekker's. There's almost always a better way to do something. Mike, glad you got things figured out. If you haven't finished yet, you might want to implement Trekker's idea. Or, you could just rewrite my function so that once it finds the first capital value, it returns the right$() string and exits the function.
    Code:
    If Asc(strTemp) < 90 And Asc(strTemp) > 65 Then 'then it's a capital
       FindLastName = Right$(strName, Len(strName) - i + 1)
       Exit Function
    End If
    This won't account for any middle initials (JohnWAndrews--->WAndrews), but I'm assuming there aren't any in there. That would handle the McDonald scenario
    Last edited by jmahaffie; 12-28-07 at 08:26.

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    And just a reminder that it's

    Code:
    <= 90 And Asc(strTemp) >= 65
    Note the equal signs... or A and Z will be omitted as "captials".
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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