George,
It might be simpler to use a function. Here is one I created and tested.
Do you know how to use a function?
Create a standard module, copy the function code and save the function and module.
In your query, you can say LastName: fLastname(namefield) and it will return a string
Code:
--namefield-- ----result of using function---
Smithsonovich Smithsonovich full name for names with no comma and no "-"
Trump-Ronald Trump for names with hyphen "-"
Trump, Ronald Trump for names with comma ","
Code:
Function fLastName(str As String) As String
If InStr(str, "-") > 0 Then
fLastName = Left(str, InStr(str, "-") - 1)
ElseIf InStr(str, ",") > 0 Then fLastName = Left(str, InStr(str, ",") - 1)
Else
fLastName = str
End If
End Function
Sample table tGeorge
id |
Namefield |
1 |
Simpson, Homer |
2 |
Trump-Ronald |
3 |
Smithsonovich |
Sample query sql
Code:
SELECT tGeorge.id, tGeorge.Namefield, fLastname([namefield]) AS extracted
FROM tGeorge;
Sample output
id |
Namefield |
extracted |
1 |
Simpson, Homer |
Simpson |
2 |
Trump-Ronald |
Trump |
3 |
Smithsonovich |
Smithsonovich |