Extracting last name from a field 2

[jgarnick]

Anyone know the best way to extract last name from a field that includes both first name and last name and drop it into a last name field, aka sort field.&nbsp;&nbsp;The last name would be the last word in the field.&nbsp;&nbsp;If using the Right function, how do you get it to stop at the space between last name and first name?&nbsp;&nbsp;Or is there a better way?<br><br>Thanks! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[jimmythegeek]

Try this, changing the bold areas to your field.<br><br>mid(<b>YourField</b>, instr(1, <b>YourField</b>, &quot; &quot+1)<br><br>HTH <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development

 

[jgarnick]

Thanks Jimmy, but it didn't work.&nbsp;&nbsp;It extracted the entire field minus the first character in the field. For example, John Jones--became ohn Jones, instead of Jones.&nbsp;&nbsp;I tried trimming the field, but it didn't help either.&nbsp;&nbsp;Any other ideas?<br><br>Thanks! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[jgarnick]

Oops, I knew by following through your statement that it should work, and guess what, I had not left a space between the quotes in my update query.&nbsp;&nbsp;Details, details....thanks again!! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[jgarnick]

I just noticed that actually it would be better to work backwards, search for the first &quot; &quot; starting from the right.&nbsp;&nbsp;This is because some of the entries have Mr. and Mrs. Bob Jones, and I only want Jones, not &quot;and Mrs. Bob Jones&quot;.&nbsp;&nbsp;Any ideas for that?<br><br>Thanks!! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[jimmythegeek]

Copy and paste the following function into a module, and save the module as whatever you want:<br><br>--------------------<br><br>Public Function GetLastName(strName As String) As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strLen As Integer, i As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strTempName As String<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;strLen = Len(strName)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;For i = strLen To 1 Step -1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Mid(strName, i, 1) &lt;&gt; &quot; &quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strTempName = Mid(strName, i, 1) & strTempName<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Exit For<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Next i<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;GetLastName = strTempName<br>End Function<br><br>--------------------<br><br>Now in your update query, in the Update To row, put the following, replacing the bolded section with the name of your &quot;Name&quot; field: GetLastName([<b>YourNameFieldHere</b>])<br><br>Now it shouldn't matter if you have<br><br>John Smith<br>John B. Smith<br>Mr. John Smith<br>Mr. John B. Smith<br><br>It should work for all of them.<br> <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development

 

[RYankowitz]

Use this:&nbsp;&nbsp;&nbsp;&nbsp;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Count As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;For Count = Len(Your_Field) - 1 To 1 Step -1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Mid$(Your_Field, Count, 1) = &quot; &quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Exit For<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;Last_Name_Field = Mid$(Your_Field, Count + 1)

 

[jgarnick]

You guys are awesome--thanks so much for your help!&nbsp;&nbsp;I used Jim's code and it worked!! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>