[AccessD] Back in the saddle

[Stuart McLachlan]

Good luck with that. Names are tricky.
Vincent Van Gogh.  
Titus von der Malsburg

That said, this is not too bad:

Function NameParts(fullname As String) As String
Dim p1, p2 As Long
Dim firstname, middlename, lastname As String
While InStr(fullname, "  ")
  fullname = Replace(fullname, "  ", " ")
Wend
p1 = InStr(fullname, " ")
If p1 > 0 Then
  p2 = InStrRev(fullname, " ")
  firstname = Left$(fullname, p1 - 1)
  lastname = Mid$(fullname, p2 + 1)
  If p1 <> p2 Then middlename = Trim$(Mid$(fullname, p1 + 1, p2 - p1 - 1))
Else
  lastname = fullname
End If
Debug.Print "CleanName: " & firstname & " " & middlename & " " & lastname
NameParts = "Name Parts: " & firstname & "|" & middlename & "|" & lastname
End Function


On 20 Apr 2024 at 1:00, John Bartow wrote:

> I'm using Access again, after many years away. I'm using it to migrate data from one messy cloud based LOB program's data to another messy cloud based LOB program. So I'm basically breaking it down to build it back up again.
> 
> I need to compare [client name] (one field) "Joe D. Testguy" to [first name] "Joe" [middle name] "D." [last name] "Testguy" concatenated to be one field for this purpose. I used to use a function to do this and have the result with only 1 space between the 3 parts even if the middle name was null. Can one of you gurus refresh my memory as to how this is done?
> 
> That's the fun part. The drag comes when someone put "Joe D Testguy and Jill D Testgal" as the [client name].
> 
> TIA
> John B
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>