How to trim a field 1

[jhb11]

I have a field that contains (lastname, firstname). I would like to be able to delete (, firstname) from all fields. What do I need to do. Thanks.

 

[ahmun]

Here's a proof-of-concept function I wrote that Extracts the first and last name out of a string with the "Last, First" structure.

Public Function GetLastName()

' This function extracts the first part of a string that is "LastName, FirstName"

Dim strLast, strFirst, strString As String
Dim intStrPos As Integer

strString = "Jones, John"

'1 -- Search for the position of the "comma" delimiter.

intStrPos = InStr(1, strString, ",", vbTextCompare)

'2 -- Get the last name using a Left Trim

strLast = Left(strString, (InStr(1, strString, ",", vbTextCompare) - 1))

'3 -- Get the First name using a combination of Trim and Right Trim

strFirst = Trim(Right(strString, Len(strString) - (InStr(1, strString, ","))))

'4 -- see the results!

MsgBox "Original String: " & strString
MsgBox "Last Name: '" & strLast & "' First Name: '" & strFirst & "'"

End Function

I haven't tried this but if you want to put it in an Access query, try to set one of the fields like this:

First: Left([tableName].[FieldNameToTrim], (InStr(1, [tableName].[FieldNameToTrim], ",", vbTextCompare) - 1))

Earnie Eng
If you are born once, you will die twice.
If you are born twice, you will die once

 

[jhb11]

Thanks,

You are a life saver.....