Macro cleaning a table? 1

[willz99ta]

Let me start by saying thanks for your help.

I know this may sound basic, but I am kind of struggling with how to write this (I have only used VBA with Excel).

I have one Access table with a bunch of entries that need to be cleaned up and another Access table that is blank that I want to populate.

I only need to clean up one of the fields, the publisher name, because many of them will say JoeyJoeJoe Inc., others with say JoeyJoeJoe Incorporated, and even others will say JoeyJoeJoeBob.

So I was going to move all of the fields in the old table to the new table, except that if the publisher field says INC. (or other variations) I would remove that and append "Inc" at the end of the text.

Any ideas?

Thank a million guys/gals,
Willz99ta

 

[snetzky]

Why not just look for the various varieties of the Inc and run queries to replace each one? There really isn't a way to do it all in one swoop.

 

[willz99ta]

I don't think that will work since I need to do this everytime I repopulate this database.
I want to make some code that:
Uses Mid to read the access field into an array by character,
check for a group of letters at the end of the array,
then change it so something I like,
and then go to the next field until the end of the data.

Does anyone have some code that does something similar?

Will

 

[willz99ta]

What I eventually did was create queries to do everything instead of messing with VBA Macros. It worked perfectly and was less technical.

 

[JoeAtWork]

I don't think that will work since I need to do this everytime I repopulate this database.

Why not make the database relational, i.e. have a separate Publisher table that has only one spelling for each name?

Not only will you never have to deal with this problem again, but you will now be able to do all the things that are possible with a real database (i.e. manipulating data in all sorts of ways to get useful information for making business decisions).

 

[LarrySteele]

Joe,

I agree with you of course. However, if you read the OP's response - "everytime I repopulate this database" - gives me the impression that this database processes external data that's out of Will's control. Those are never fun to work with

Of course, I could be wrong. Wouldn't be the first time.

- Larry