CODEWITHSUNDEEP
ms-accessms-access-2007
Michael
Michael

Reputation: 305

Trim Name in MS Access

I am very new to MS Access. I have list of names like this in a column in my access and would need to trim the names like below.

Names:

  Clooney, George Timothy
  Willam Pitt, Brad
  Wilson, Larry

Expected Output:

  Clooney, George
  Willam, Brad
  Wilson, Larry

I need to have the first word before and after the comma. Also on top of this I need to add a space after comma.

Really appreciate suggestions.

Upvotes: 1

Views: 163

Answers (1)

PaulFrancis
PaulFrancis

Reputation: 5809

You might be able to achieve this using a User Defined Function. Something like this might be helpful. Copy the code into a Standard module and then compile it. 

Public Function getFirstName(inputStr As String) As String
'********************
'Code Courtesy of
'  Paul Eugin
'********************
    Dim tmpArr() As String, retStr As String
    Dim iCtr As Integer, charPos As Integer

    'Strip the Input String to an Array.
    tmpArr = Split(inputStr, ",")

    'Loop through the Array
    For iCtr = 0 To UBound(tmpArr)
        'Find the position of the SPACE character
        charPos = InStr(Trim(tmpArr(iCtr)), " ")

        'If the position is not found, return the whole string
        If charPos = 0 Then charPos = Len(tmpArr(iCtr))

        'Append the result String
        retStr = retStr & Trim(Left(tmpArr(iCtr), charPos)) & ", "
    Next

    'Finally return the actual generated String
    getFirstName = Left(retStr, Len(retStr) - 2)
End Function

To use this in a immediate window, use it as,

? getFirstName("Wilson, Larry")
Wilson, Larry

? getFirstName("Clooney, George Timothy")
Clooney, George

This can also be used in a Query. 

SELECT 
    yourTableName.yourFieldName, 
    getFirstName(yourTableName.yourFieldName) As NewFieldName 
FROM
    yourTableName;

Upvotes: 1

Related Questions