Results 1 to 12 of 12
  1. #1
    Vetgeorge is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Florida
    Posts
    29

    Query to retrieve last names, it works, except for partial hyphenated names

    Query to retrieve last names, it works, except for partial hyphenated names. I have this as an expression to retrieve/extract last name when a comma exists:



    Left([CustomerName],InStr([CustomerName],",")-1)

    However, I have a problem when there isn’t a comma in the name field. The name list I have to work with only has a certain number of characters, therefore, long hyphenated names get cut-off, so there isn’t a comma.How can I retrieve the last name in that circumstance? Can you combine multiple search expressions in the same query?

    Example:
    SMITH, JOHN SMITH
    DOE, JANE DOE
    JOHNSON-JOH ??

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How can I retrieve the last name in that circumstance?
    Where does the data come from?
    If it is not available to your system, then you don't have it.
    If you are saying that your field size is too small to handle all characters in your data, then you have to consider changing the field size.
    If your logic depends on the existence of a comma, and there is no comma, then you have a logic issue.
    Is there another character that distinguishes first name from last name?

    Do you handle names like?
    -de la Hoya Oscar
    -van't Goor, Ali

    More info required to give more focused response.

  3. #3
    Vetgeorge is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Florida
    Posts
    29
    Upon further searching in this forum, I found a solution that worked, for taking care of the hyphen problem:
    ExtractLastName: IIf(InStr([NameField],",")>0,Left([NameField],InStr(1,[NameField],",")-1),Mid([NameField],1,InStr(1,[NameField],"-")-1))

    Orange you did bring up a good point though, how would I get the partial last name where no comma or hyphen exists, such as MASTERSO (MASTERSON)?

    Quote Originally Posted by orange View Post

    Where does the data come from? I get the data from our antiquated Lab Information System (LIS), which is text based, I ask for data, it spits out a text file. The LIS has limited the field size for the names, not me.

    If it is not available to your system, then you don't have it.
    If you are saying that your field size is too small to handle all characters in your data, then you have to consider changing the field size. My field size is not limited, the information I receive is limited
    If your logic depends on the existence of a comma, and there is no comma, then you have a logic issue.
    Is there another character that distinguishes first name from last name? All names are Last, First -unless there are too many characters, then the name gets cut, so sometimes a name field will be just a partial last name (JOHNSON-JO (for JOHNSON-JOHNSON)or MASTERSO (for MASTERSON)

    Do you handle names like?
    -de la Hoya Oscar
    -van't Goor, Ali

    More info required to give more focused response.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Did you want to use the Thread Tools at the top of the thread to mark this thread as Solved?

  5. #5
    Vetgeorge is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Florida
    Posts
    29
    Solved two of the problems by combining the two expressions, but a third problems arose: how would I get the partial last name where no comma or hyphen exists, such as MASTERSO (MASTERSON)?

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think you are stuck with this sort of logic (no comma and no hyphen) use the whole field

    ExtractLastName: IIf(InStr([NameField],",")=0 and Instr(NameField,"-") = 0, then you use NameField

  7. #7
    Vetgeorge is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2012
    Location
    Florida
    Posts
    29
    I tried your whole field expression by itself and it returned the name that was a partial without comma or hyphen, so that worked:
    ExtractLastName: IIf(InStr([NameField],",")=0 And (InStr([NameField],"-")=0),([NameField]))

    How would I incorporate this additional expression into my existing one which returns the names with comma and hyphen:
    ExtractLastName: IIf(InStr([NameField],",")>0,Left([NameField],InStr(1,[NameField],",")-1),Mid([NameField],1,InStr(1,[NameField],"-")-1))

    Thank you for your helpful reply Orange

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  9. #9
    Vetgeorge is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2012
    Location
    Florida
    Posts
    29
    The codes I used that worked are in the post directly above, timestamp 10:16 AM:
    I tried your whole field expression by itself and it returned the name that was a partial without comma or hyphen, so that worked:
    ExtractLastName: IIf(InStr([NameField],",")=0 And (InStr([NameField],"-")=0),([NameField]))

    How would I incorporate this additional expression into my existing one which returns the names with comma and hyphen:
    ExtractLastName: IIf(InStr([NameField],",")>0,Left([NameField],InStr(1,[NameField],",")-1),Mid([NameField],1,InStr(1,[NameField],"-")-1))

    Quote Originally Posted by orange View Post
    George,

    I see you have marked this solved. Can you show us the expression you used?

  10. #10
    Vetgeorge is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2012
    Location
    Florida
    Posts
    29

    Updated: all codes and examples

    ExtractComma: Left([NameField],InStr(1,[NameField],",")-1)
    ExtractHyphenated: Mid([NameField],1,InStr(1,[NameField],"-")-1)
    ExtractNoSymbols: IIf(InStr([NameField],",")=0 And (InStr([NameField],"-")=0),([NameField]))

    ExtractCombinedCommaHyphen: IIf(InStr([NameField],",")>0,Left([NameField],InStr(1,[NameField],",")-1),Mid([NameField],1,InStr(1,[NameField],"-")-1))

    I still need assistance in adding the ExtractNoSymbols (last line in table below) to the Combined string above^

    NameField ExtractComma ExtractHyphenated ExtractNoSymbols ExtractCombinedCommaHyphen
    GRIMALDI, ROBERT GRIMALDI #Func!
    GRIMALDI
    FAVORS-STRICK #Func! FAVORS
    FAVORS
    WAINRIGHT, AE WAINRIGHT #Func!
    WAINRIGHT
    CAGNEY, LACEY CAGNEY #Func!
    CAGNEY
    ALBRECHT-WATERS #Func! ALBRECHT
    ALBRECHT
    JARA, PHILIP JARA #Func!
    JARA
    MARCH, JAMES MARCH #Func!
    MARCH
    BURRELL, JOE BURRELL #Func!
    BURRELL
    ODONNELL-SM #Func! ODONNELL
    ODONNELL
    AUGSBURGER #Func! #Func! AUGSBURGER #Func!

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    George,

    It might be simpler to use a function. Here is one I created and tested.
    Do you know how to use a function?

    Create a standard module, copy the function code and save the function and module.

    In your query, you can say LastName: fLastname(namefield) and it will return a string

    Code:
     --namefield--             ----result of using function---
    Smithsonovich              Smithsonovich   full name for names with no comma and no "-"
    Trump-Ronald               Trump   for names with hyphen "-"
    Trump, Ronald             Trump   for names with comma ","
    Code:
    Function fLastName(str As String) As String
        If InStr(str, "-") > 0 Then
            fLastName = Left(str, InStr(str, "-") - 1)
        ElseIf InStr(str, ",") > 0 Then fLastName = Left(str, InStr(str, ",") - 1)
        Else
            fLastName = str
        End If
    
    End Function
    Sample table tGeorge

    id Namefield
    1 Simpson, Homer
    2 Trump-Ronald
    3 Smithsonovich


    Sample query sql

    Code:
    SELECT tGeorge.id, tGeorge.Namefield, fLastname([namefield]) AS extracted
    FROM tGeorge;
    Sample output

    id Namefield extracted
    1 Simpson, Homer Simpson
    2 Trump-Ronald Trump
    3 Smithsonovich Smithsonovich

  12. #12
    Vetgeorge is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2012
    Location
    Florida
    Posts
    29

    Thumbs up

    Thank you orange! It works. Your instructions were very easy to follow, even though I have never created a function to use in a query.

    To reiterate your steps and show my code:

    Table name: tblImported; Fields: ID, NameField

    1. Create Module:
    Function fLastName(str As String) As String
    If InStr(str, "-") > 0 Then
    fLastName = Left(str, InStr(str, "-") - 1)
    ElseIf InStr(str, ",") > 0 Then fLastName = Left(str, InStr(str, ",") - 1)
    Else
    fLastName = str
    End If
    End Function

    2. Create query (SQL view):

    SELECT tblImported.ID, tblImported.NameField, fLastname([NameField]) AS LastName
    FROM tblImported;

    Results of the query:

    ID NameField LastName
    1 GRIMALDI, ROBERT GRIMALDI
    2 FAVORS-STRICK FAVORS
    3 WAINRIGHT, AE WAINRIGHT
    4 CAGNEY, LACEY CAGNEY
    5 ALBRECHT-WATERS ALBRECHT
    6 JARA, PHILIP JARA
    7 MARCH, JAMES MARCH
    8 BURRELL, JOE BURRELL
    9 ODONNELL-SM ODONNELL
    10 AUGSBURGER AUGSBURGER

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Looking up Names in a Query
    By CKelly55 in forum Queries
    Replies: 6
    Last Post: 10-15-2014, 01:01 PM
  2. To query out city names
    By lizzywu in forum Queries
    Replies: 2
    Last Post: 11-26-2012, 11:59 AM
  3. Query Entity Names, and Column Names
    By Accidental DBA in forum Queries
    Replies: 1
    Last Post: 07-22-2011, 02:38 PM
  4. Replies: 5
    Last Post: 04-24-2011, 03:14 AM
  5. How to retrieve field names in a table
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 12-05-2010, 09:09 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums