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

    Unanswered: Parse name field into fname and lname

    I'm a Novice User using Access 2003
    Tables are via ODBC (i.e., cannot alter fields)

    I have a report that uses the field OrderRepName (text string) and is formatted as lastname, firstname, middleinitial (No prefixes or suffixes, etc.). Sometimes the field is blank and sometimes there is no middle initial.

    I found the following code on the Internet and it seems like it will work except I get an error message regarding Null values, and it doesn't seem to handle middle initials. (I modified it slightly replacing the word "underscore" with "space").

    Can someone help me modify the following code, or suggest something else?

    Function ParseFirstComp(pValue) As String
    Dim LPosition As Integer
    'Find postion of space
    LPosition = InStr(pValue, " ")
    'Return the portion of the string before the space
    If LPosition > 0 Then
    ParseFirstComp = Left(pValue, LPosition - 1)
    Else
    ParseFirstComp = ""
    End If
    End Function

    Function ParseSecondComp(pValue) As String
    Dim LPosition As Integer
    'Find postion of space
    LPosition = InStr(pValue, "_")
    'Return the portion of the string after the space
    If LPosition > 0 Then
    ParseSecondComp = Mid(pValue, LPosition + 1)
    Else
    ParseSecondComp = ""
    End If
    End Function


    This first function called ParseFirstComp will return the portion of the string before the space. The second function called ParseSecondComp will return the portion of the string after the space.
    Next, you'll need to use this function in your query

    (picture - but I can't paste it into this posting)

    In the example above, we've used both functions to parse a field called Name. You will need to substitute your field name with the ParseFirstComp and ParseSecondComp functions. So we've typed ParseFirstComp([Name]) in the first field and ParseSecondComp([Name]) in the second field. Access assigns the field name of "Expr1" and "Expr2" - you can always overwrite this.

    Now, when we run the query, we'll get the following results:

    (Picture - but I can't paste it into this posting)

    Thanks,

    Krazy
    Last edited by KrazyKasper; 05-13-08 at 12:54.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are having problems with null values, then test for null values on the first line of each function and set a suitable return value

    Code:
    Function ParseFirstComp(pValue) As String
    ParseFirstComp = "" 'set the default return value
    if NOT isnull(pValue) then 
      Dim LPosition As Integer
      'Find postion of space
      LPosition = InStr(pValue, " ")
      'Return the portion of the string before the space
      If LPosition > 0 Then
        ParseFirstComp = Left(pValue, LPosition - 1)
      End If
    end if
    End Function
    
    Function ParseSecondComp(pValue) As String
    ParseSecondComp = "" 'set the default return value
    if NOT isnull(pValue) then 
      Dim LPosition As Integer
      'Find postion of space
      LPosition = InStr(pValue, "_")
      'Return the portion of the string after the space
      If LPosition > 0 Then
        ParseSecondComp = Mid(pValue, LPosition + 1)
      End If
    end if
    End Function
    the alternative is to use the NZ function in SQL
    I chop and change between VBA and other languages so ISNULL may not be a valid construct in Access.. it could be if myvariable <> NULL....
    I'd rather be riding on the Tiger 800 or the Norton

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

    Parse name field into fname and lname

    Thanks!
    The code works quite well.
    One minor problem:
    The field was originally formatted as lastname, firstname.
    The code doesn't remove the comma after the last name so I end up with firstname lastname,
    Appreciate if you could help me with that.
    Thanks again.

    Krazy

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are getting an additional character then it means that the number of characters being brought across is to large...

    rather than provide an answer can I suggest you try to understand what this fragment of code actually does.

    what Id suggest you do is try to work pout what the code is actually doing (settign a debug point and stepping through the code, examinign all the varaibels is a good starting point for that).
    I'd rather be riding on the Tiger 800 or the Norton

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

    Parsing name to fname lname

    I don't quite understand your reply.
    Following is the code I'm using. It works fine except it leaves a comma after the last name.

    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 - 1)
    End If
    End If
    End Function

    Function ParseSecondComp(OrderRepName) As String
    ParseSecondComp = "" '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 after the space
    If LPosition > 0 Then
    ParseSecondComp = Mid(OrderRepName, LPosition + 1)
    End If
    End If
    End Function

    Krazy

Posting Permissions

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