Text manipulation in Access

[Guest]

Trying to make these look like real names:
Smith, John Etux Gertrud
Jones, Anita A Etvir Charles W
Richards, Allen M & Linda L

I want them to look like:
John and Gertrud Smith

Problem is, there is no predicting if there will be an initial or not, or an
"etux" or not, etc. There is always a last name and comma.

Anyone have any ideas? I can get around with text manipulation but this is
beyond me! Thanks for any help.
Ronda

 

[John Spencer]

This is tough and it will never be correct in every case.

Spencer, Jr., John and Susan

LastName: Left(YourField, Instr(1,Yourfield,",") -1)
RestOfName: Mid(YourField, Instr(1,YourField,",") + 1)
Combine those

Trim(Mid(YourField, Instr(1,YourField,",")+1)) & " " & Trim(Left(YourField, Instr(1,Yourfield,",")-1))

Now, to change & to AND and etux to and

Replace(Replace(Trim(Mid(YourField, Instr(1,YourField,",")+1)) & " " &
Trim(Left(YourField, Instr(1,Yourfield,",")-1))," & ", " and "), " etux ", " and ")

Ain't that beeeyoootiful! and it will fail if there is no comma or if the value
in the name field happens to be null

I would rather use a custom function to do this. Untested

Public Function fMakeName(sNameIn)
Dim strOut as String

If Len(trim(sNameIn & "") = 0) Then
fMakeName = sNameIN
ElseIf Instr(1,sNameIn,",") = 0 then
fMakeName = sNameIn
Else
strOut = Trim(Mid(sNameIn, Instr(1,sNameIn,",") + 1))
strOut = Replace(strOut," etux ", " and ")
strOut = Replace(strOut," & ", " and ")
strOut = strOut & " " & Trim(Left(YourField, Instr(1,Yourfield,",") -1))
fMakeName = strOut
End If

End Function

Copy that into a module and save it (module name must be different then function name).
In the query or on the report you should be able to call the function. In query

Field: FullName: fMakeName([Yourtablename].[YourfieldName])

 

[Guest]

Thanks John this looks great, so I'm trying it and I must have something
wrong. I changed the YourField to sNameIn on the fMakeName function. I
called the module MakeNameModule1. My field is called Owner Name, and I had
that without the table name before and it displays. So, I put
=fMakeName([Owner Name]) there instead, and it doesn't make any changes.
It's like it doesn't actually do it, but it does still display Owner Name in
its original form.

When I first created the new Module, it said Option Compare Database at the
top, and I deleted it and put your function code in. Should I have left it
there?
Thanks!
Ronda

This is tough and it will never be correct in every case.

Spencer, Jr., John and Susan

LastName: Left(YourField, Instr(1,Yourfield,",") -1)
RestOfName: Mid(YourField, Instr(1,YourField,",") + 1)
Combine those

Trim(Mid(YourField, Instr(1,YourField,",")+1)) & " " & Trim(Left(YourField, Instr(1,Yourfield,",")-1))

Now, to change & to AND and etux to and

Replace(Replace(Trim(Mid(YourField, Instr(1,YourField,",")+1)) & " " &
Trim(Left(YourField, Instr(1,Yourfield,",")-1))," & ", " and "), " etux ", " and ")

Ain't that beeeyoootiful! and it will fail if there is no comma or if the value
in the name field happens to be null

I would rather use a custom function to do this. Untested

Public Function fMakeName(sNameIn)
Dim strOut as String

If Len(trim(sNameIn & "") = 0) Then
fMakeName = sNameIN
ElseIf Instr(1,sNameIn,",") = 0 then
fMakeName = sNameIn
Else
strOut = Trim(Mid(sNameIn, Instr(1,sNameIn,",") + 1))
strOut = Replace(strOut," etux ", " and ")
strOut = Replace(strOut," & ", " and ")
strOut = strOut & " " & Trim(Left(YourField, Instr(1,Yourfield,",") -1))
fMakeName = strOut
End If

End Function

Copy that into a module and save it (module name must be different then function name).
In the query or on the report you should be able to call the function. In query

Field: FullName: fMakeName([Yourtablename].[YourfieldName])

Trying to make these look like real names:
Smith, John Etux Gertrud
Jones, Anita A Etvir Charles W
Richards, Allen M & Linda L

I want them to look like:
John and Gertrud Smith

Problem is, there is no predicting if there will be an initial or not, or an
"etux" or not, etc. There is always a last name and comma.

Anyone have any ideas? I can get around with text manipulation but this is
beyond me! Thanks for any help.
Ronda

 

[John Spencer]

My sincere apologies for sending you untested code that does not work.
I've tested the revised code and it does work although it can still give you
erroneous results. For instance, "Spencer, Jr John & Susan" will be
returned as "Jr John and Sue Spencer". The best solution is obviously to
store the parts of the name in separate fields and combine them as needed.

Public Function fMakeName(sNameIn)
Dim strOut As String

If Len(Trim(sNameIn & "")) = 0 Then
fMakeName = sNameIn
ElseIf InStr(1, sNameIn, ",") = 0 Then
fMakeName = sNameIn
Else
strOut = Trim(Mid(sNameIn, InStr(1, sNameIn, ",") + 1))
strOut = Replace(strOut, " etux ", " and ")
strOut = Replace(strOut, " & ", " and ")
strOut = strOut & " " & _
Trim(Left(sNameIn, InStr(1, sNameIn, ",") - 1))
fMakeName = strOut
End If

End Function

End Function

Thanks John this looks great, so I'm trying it and I must have something
wrong. I changed the YourField to sNameIn on the fMakeName function. I
called the module MakeNameModule1. My field is called Owner Name, and I
had
that without the table name before and it displays. So, I put
=fMakeName([Owner Name]) there instead, and it doesn't make any changes.
It's like it doesn't actually do it, but it does still display Owner Name
in
its original form.

When I first created the new Module, it said Option Compare Database at
the
top, and I deleted it and put your function code in. Should I have left
it
there?
Thanks!
Ronda

This is tough and it will never be correct in every case.

Spencer, Jr., John and Susan

LastName: Left(YourField, Instr(1,Yourfield,",") -1)
RestOfName: Mid(YourField, Instr(1,YourField,",") + 1)
Combine those

Trim(Mid(YourField, Instr(1,YourField,",")+1)) & " " &
Trim(Left(YourField, Instr(1,Yourfield,",")-1))

Now, to change & to AND and etux to and

Replace(Replace(Trim(Mid(YourField, Instr(1,YourField,",")+1)) & " " &
Trim(Left(YourField, Instr(1,Yourfield,",")-1))," & ", " and "), " etux
", " and ")

Ain't that beeeyoootiful! and it will fail if there is no comma or if
the value
in the name field happens to be null

I would rather use a custom function to do this. Untested

Public Function fMakeName(sNameIn)
Dim strOut as String

If Len(trim(sNameIn & "") = 0) Then
fMakeName = sNameIN
ElseIf Instr(1,sNameIn,",") = 0 then
fMakeName = sNameIn
Else
strOut = Trim(Mid(sNameIn, Instr(1,sNameIn,",") + 1))
strOut = Replace(strOut," etux ", " and ")
strOut = Replace(strOut," & ", " and ")
strOut = strOut & " " & Trim(Left(YourField,
Instr(1,Yourfield,",") -1))
fMakeName = strOut
End If

End Function

Copy that into a module and save it (module name must be different then
function name).
In the query or on the report you should be able to call the function.
In query

Field: FullName: fMakeName([Yourtablename].[YourfieldName])

Trying to make these look like real names:
Smith, John Etux Gertrud
Jones, Anita A Etvir Charles W
Richards, Allen M & Linda L

I want them to look like:
John and Gertrud Smith

Problem is, there is no predicting if there will be an initial or not,
or an
"etux" or not, etc. There is always a last name and comma.

Anyone have any ideas? I can get around with text manipulation but
this is
beyond me! Thanks for any help.
Ronda