Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Cool Unanswered: Access 2003 Parsing Employee Name

    Access 2003 Parsing Employee Name

    The employee name in my table (accessed via ODBC) is a single field EmpName showing LastName, FirstName and sometimes all caps. e.g.:
    JONES, JOHN
    Thompson, Norman
    Vee, Maureen
    JOHNSON, VINCENT

    I parse the field in my query to separate the name and then String Convert to put them together as FirstName LastName, and change the case to Normal.
    My code is: =StrConv([Expr2] & " " & [Expr1],3)

    I also use Trim to put the names into an e-mail format.
    My code for that is: =Trim([Expr2] & "." & [Expr1] & "@mycompany.com")

    However in both cases I end up with a comma after the Last Name
    e.g.:
    John Jones,
    john.jones,@mycompany.com

    Can someone help me to remove the comma after the last name?
    Thanks,


  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    How are you parsing out the last name now? The method to get rid of the comma depends on that (unless you want to add an extra step).
    Paul

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Expression: Left([FieldName], Len([FieldName])-1)

    This will remove the last character from FieldName and show it as Expression.
    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

  4. #4
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56
    PBaldy,

    I'm parsing the name in my query.
    Expr1: ParseFirstComp([OrderRepName])
    Expr2: ParseSecondComp([OrderRepName])

    Then in my report I use:
    =StrConv([Expr2] & " " & [Expr1],3) for the Order Rep Name field and
    =Trim([Expr2] & "." & [Expr1] & "@mycompany.com") for the e-mail field.

    StarTrekker,
    Not sure where the string Left([FieldName], Len([FieldName])-1)
    comes in that you suggested.


    Krazy (Bill) Kasper

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Alter the table, adding a column for firstname and one for lastname.

    then run an update query based off of your parsing to populate these columns.
    Code:
    UPDATE my_table
    SET    firstname = [Expr2]
         , lastname = [Expr1]
    Replace [Expr1|2] with the actual parsing though

    You can then drop your fullname column and be done with all this faffing
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56
    georgev -
    Cannot change the original table (accessed via ODBC).
    Prefer not to have to create a new (temporary) table.

    Krazy (Bill) Kasper

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Create a view on your base table with two derived columns, the parsed firstname and lastname instead then

    Post the SQL you've used for parsing so far if you're stuck.
    George
    Home | Blog

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    StarTrekker's solution is the extra step I mentioned, but I would do it within the functions you're using.
    Paul

  9. #9
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    ParsedName: Replace(StrConv([Expr2] & " " & [Expr1],3)
    ,",","")

    Replaces any "," found with ""

    Hope this helps
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  10. #10
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Access 2003 Parsing Employee Name

    I've got it.
    Thanks to everyone who suggested solutions.
    All I had to do was change my VBA code from -1 to -2.
    See following.

    Function ParseFirstComp(OrderRepName) As String
    ParseFirstComp = "" 'set the default return value
    If Not IsNull(OrderRepName) Then
    Dim LPosition As Integer
    'Find postion of space
    LPosition = InStr(OrderRepName, " ")
    'Return the portion of the string before the space
    If LPosition > 0 Then
    ParseFirstComp = Left(OrderRepName, LPosition - 2)
    End If
    End If
    End Function


    Thanks again.

Posting Permissions

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