Trim to the right

[Moxioron]

Hello,

I am trying to perform a Right(Trim on a last name to shed the JR

For examle Smith JR....I just want Smith for the field.

Thanks for your help.

 

[Moxioron]

Left([tbl_Rewards Activity Report_Updated].[Last Name],InStr(1,[tbl_Rewards Activity Report_Updated].[Last Name]," ")-1)

 

[Moxioron]

That didn't work. If there is nothing after the last name I get the #func!

 

[jdraw]

Is it only "JR" that you are trying to exclude in names?
What about "Tom Van der Meer" or "Ali Vant Goor" type names?

 

[Moxioron]

Any non name suffix e.g. JR or III

 

[Moxioron]

This works great if there is a suffix

Left([Last Name],InStr(1,[Last Name]," ")-1)

But if there is not one, I get the #Func!

 

[bob fitz]

Try:
Left([Last Name],InStr(1,[Last Name] & " "," ")-1

 

[jdraw]

How will you prevent "unintentional deletion" of pats of names such as I mentioned in post 4?
We had to do similar "cleansing" on names such as

Smith PhD
Smyth Jr
Smyth Sr
Del Rio BSc

Good luck

 

[apr pillai]

Left([Last Name],InStr(1,[Last Name] & " "," ")-1

Try this:

Left([Last Name],InstrRev(RTrim([Last Name]),space(1))-1)

The -1 in the Left() Function can run into problems when there is no segmentation in Lastname.

I think a Public Function, like the following code, in the Standard Module will be safer and easier to use:

Public Function R_Trim(ByVal strval As String) As String
Dim s_Out As String, s_in As String, i As Integer

On Error Resume Next
s_in = RTrim(strval)
i = InStrRev(s_in, Space(1))
If i = 0 Then
   R_Trim = s_in
   Exit Function
End If
s_Out = Left(s_in, InStrRev(s_in, Space(1)) - 1)
R_Trim = s_Out

End Function

Call the function with the field name as parameter: R_Trim([LastName])
or
[LastName]=R_Trim([LastName]) to modify and store back in the same field.