Replace Co with Company

  • Thread starter Thread starter Sandspur
  • Start date Start date
S

Sandspur

I have a [CompanyName] field that I need to replace Co with Company

Most of the time Co is at the end of the CompanyName but sometimes it is in
the middle of CompanyName
Ajax Construction Co
Big Construction Co Inc

Thank you
 
Be careful. Be very careful. You could use do something as simple, but slow,
as open up the table; click on that column, then do a Find and Replace on
"co".

However (this is the careful part) Construction could come out looking like
Companynstruction!!!!

So make very sure to back up the entire database and that table before doing
this.

Actually unless you have thousands of records or this is something that you
have to do frequently, you might be better off just doing it manually.

I'd do a find on the CompanyName field; searching any part of the field; on
{space}co . This would jump you through records that didn't have any co's
and actually place you on the co's there. Then you could decide if it should
be company or left alone.
 
Is it possible to add a trailing space at the end of [CompanyName] field

then i could do a find and replace useing " co "

Jerry Whittle said:
Be careful. Be very careful. You could use do something as simple, but slow,
as open up the table; click on that column, then do a Find and Replace on
"co".

However (this is the careful part) Construction could come out looking like
Companynstruction!!!!

So make very sure to back up the entire database and that table before doing
this.

Actually unless you have thousands of records or this is something that you
have to do frequently, you might be better off just doing it manually.

I'd do a find on the CompanyName field; searching any part of the field; on
{space}co . This would jump you through records that didn't have any co's
and actually place you on the co's there. Then you could decide if it should
be company or left alone.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Sandspur said:
I have a [CompanyName] field that I need to replace Co with Company

Most of the time Co is at the end of the CompanyName but sometimes it is in
the middle of CompanyName
Ajax Construction Co
Big Construction Co Inc

Thank you
Click to expand...
Click to expand...
 
Great idea except if someone put a period like "co.".

It also won't work where "co" are the last characters in the field. You
might be able to use the Right function to match the last 2 characters of the
field and then just changed the.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Sandspur said:
Is it possible to add a trailing space at the end of [CompanyName] field

then i could do a find and replace useing " co "

Jerry Whittle said:
Be careful. Be very careful. You could use do something as simple, but slow,
as open up the table; click on that column, then do a Find and Replace on
"co".

However (this is the careful part) Construction could come out looking like
Companynstruction!!!!

So make very sure to back up the entire database and that table before doing
this.

Actually unless you have thousands of records or this is something that you
have to do frequently, you might be better off just doing it manually.

I'd do a find on the CompanyName field; searching any part of the field; on
{space}co . This would jump you through records that didn't have any co's
and actually place you on the co's there. Then you could decide if it should
be company or left alone.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Sandspur said:
I have a [CompanyName] field that I need to replace Co with Company

Most of the time Co is at the end of the CompanyName but sometimes it is in
the middle of CompanyName
Ajax Construction Co
Big Construction Co Inc

Thank you
Click to expand...
Click to expand...
Click to expand...
 
Is it possible to add a trailing space at the end of [CompanyName] field

then i could do a find and replace useing " co "
Click to expand...

No, but you could use a query criterion

LIKE "* Co"

with no trailing wildcard to find names ending in a blank and Co; use

LIKE "* Co."

to find those with a period and update them separately.
 
Sandspur said:
I have a [CompanyName] field that I need to replace Co with Company

Most of the time Co is at the end of the CompanyName but sometimes it is
in
the middle of CompanyName
Ajax Construction Co
Big Construction Co Inc

Thank you
Click to expand...

I am very rusty and cannot test, but believe [company name] & " " LIKE " Co
" would do what you asked
 
STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE [SomeTable]
SET [CompanyName] =
Left([CompanyName],Instr(1,[CompanyName] & " "," Co ")-1) & " Company " &
Mid([CompanyName],Instr(1,[CompanyName] & " "," Co ")+4)
WHERE [Company Name] & " " LIKE "* Co *"

In query design view
== Add your table
== Add your company name field Twice
== Modify the second instance to append a space to the end by typing
& " "
after the field name
== Enter criteria under this field of
LIKE "* Co *"
== Select Query: Update from the menu
== In the UPDATE row under the first Company name enter
Left([CompanyName],Instr(1,[CompanyName] & " "," Co ")-1) & " Company " &
Mid([CompanyName],Instr(1,[CompanyName] & " "," Co ")+4)

You could do a similar query for Co. You would need to adjust the criteria to
LIKE "* Co. *" and adjust the update clause by changing the +4 to +5.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top