Jeff Hamilton wrote:
I want to copy the following data from field Owner to field
PropertyOwner:
Desired Result
HAMILTON,JEFF JEFF HAMILTON
JOHNSON, JIMMY JIMMY JOHNSON
JONES, MARY & DAVID MARY & DAVID JONES
SMITHSON INDUSTRIES SMITHSON INDUSTRIES
GONZALES, TRUST GONZALEZ TRUST
DAVIDIAN,TR DAVIDIAN TR
CHO,TR ET AL CHO TR ET AL
O'BANNON, LIVING TRUST O'BANNON LIVING TRUST
I can deal with the first 5 examples, but the variations of TRUST are
killing me. Can anyone help me? I'm not a programmer. I have a sample
table, query and VBA module that I think is close to the right thing.
I can email to someone who responds.
Thanks
I see that Smithson industries did not have a comma. Those with Trust
do. I work with A97 and there is no built in Access function to remove
commas. Here is an example of one
Dim strName As "Jeff, Hamilton
Debug.Print Left(strName,Instr(strName,",") - 1) & " " & _
Trim(Mid(strName(Instr(strName,",") + 1))
This will print Jeff Hamilton, The first Left/Instr finds the comma and
takes all of the characters to the left of the comma to 1 character
before the command and the second Mid/Instr takes all characters after
the comma and concatenates it it with the first on. It adds a space
between commas and trims spaces in the second Mid/Instr.
You could build a query. In one column enter
Instr({YourTableFieldName]," Trust") > 0 and in the criteria row enter
True. In another column, drag the field name to update, make the query
and update, and in the update row put my routine excluding the
Debug.Print.
I think you will have to make multiple passes since you may have a "space
Trust", and ", Trust", a ",Trust" . Then do the same for your TRs.