Dealing with Name data

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

re: Dealing with Name data

Jeff Hamilton wrote:
[color=blue]
> 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[/color]

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.

re: Dealing with Name data

Jeff Hamilton wrote:
[color=blue]
> I want to copy the following data from field Owner to field
> PropertyOwner:
> Desired Result[/color]

If you do mass updates using a routine line the one I posted, I recommend
you first click on the table and then do a CTRL+C and then a CTRL+V to
create a copy of the table. You may not want to do mass updates without
a backup....this ensure you can start over if you mess it up.

re: Dealing with Name data

Thanks very much for your reply. I appreciate the fact that you
explained the code--that helps me to learn and maybe I don't have to beg
for help so much in the future.

I received a reply from another source that uses VBA code in an Access
module that solves the problem. I don't remember if A97 supports the
same features, but if you're interested, send me an email and I'll
forward the code to you.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Dealing with Name data

Jeff Hamilton wrote:
[color=blue]
> Thanks very much for your reply. I appreciate the fact that you
> explained the code--that helps me to learn and maybe I don't have to beg
> for help so much in the future.
>
> I received a reply from another source that uses VBA code in an Access
> module that solves the problem. I don't remember if A97 supports the
> same features, but if you're interested, send me an email and I'll
> forward the code to you.
>[/color]

People that post responses here post 1) to the poster, and 2) to everyone
else. You may have a problem that others will have in the future. They may
read this thread months from now and go "AhhHa!, that's the solution!" Many
of us use Google to find answers to our problems first before posting a
question. Google archives the messages in the newsgroup. Private replies,
as well as the solution, are private. Nobody here cares whether or not you
received a solution here or privately. Often times people will post the
solution they came up on their own, or in your case privately, so everyone
can benefit.

The bottom line....this newsgroup is a repository of knowledge. Private
responses are worthless to the knowledge base.

So thank you for your offer, but no thanks.