Replace Function

  • Thread starter Thread starter Matthew DeAngelis
  • Start date Start date
M

Matthew DeAngelis

Hi everyone,

I am migrating an existing, badly designed database to a shiny new,
hopefully better designed one, so you'll likely see me here a lot in
the next couple of months.

One of the problems I am trying to solve is that every time a new deal
is logged, the company information is reinput with a (1), (2), (3),
etc. Once I have linked all of the deals to one company, I will want
to trim these numbers off of the end. Since there are almost two
thousand records, I would rather not do this by hand.

I was toying with the Replace function, but I am pretty new to using
these. I built the Update Query in Design View and inserted the
function into the "Update To" field. Here is what I have:

UPDATE [tbl: Data - Companies] SET [tbl: Data - Companies].Company =
Replace([tbl: Data - Companies]![Company],"Like (*)","")
WITH OWNERACCESS OPTION;

The query runs just fine, except that the (numbers) are still there,
and the data appears entirely unchanged despite the warning that I will
be updating every record.

What am I doing wrong?


Thanks,
Matt
 
Hi everyone,

I am migrating an existing, badly designed database to a shiny new,
hopefully better designed one, so you'll likely see me here a lot in
the next couple of months.

One of the problems I am trying to solve is that every time a new deal
is logged, the company information is reinput with a (1), (2), (3),
etc. Once I have linked all of the deals to one company, I will want
to trim these numbers off of the end. Since there are almost two
thousand records, I would rather not do this by hand.

I was toying with the Replace function, but I am pretty new to using
these. I built the Update Query in Design View and inserted the
function into the "Update To" field. Here is what I have:

UPDATE [tbl: Data - Companies] SET [tbl: Data - Companies].Company =
Replace([tbl: Data - Companies]![Company],"Like (*)","")
WITH OWNERACCESS OPTION;

The query runs just fine, except that the (numbers) are still there,
and the data appears entirely unchanged despite the warning that I will
be updating every record.

What am I doing wrong?

Thanks,
Matt
Click to expand...


Set [Company] = Left([Company],InStr([Company],"(")-1)
 
Set [Company] = Left([Company],InStr([Company],"(")-1)
Click to expand...

Thanks for your help.

I tried inserting your code so that my SQL now looks like this:

UPDATE [tbl: Data - Companies] SET [tbl: Data - Companies].Company =
Left([Company],InStr([Company],"(")-1)
WITH OWNERACCESS OPTION;

Now, when I run it, it deletes almost all of the Company names, leaving
those fields blank. None of the remaining company names have a
(number) after it, which I guess is a good sign, but you can see why
losing my company names might be a bit worse <g>.

Perhaps I should be more specific about what I am trying to accomplish.
Right now, there are a number of records that look like this:

Company1 (1)
Company1 (2)
Company1 (3)
Company2 (1)
Company2 (2)
Company2 (3)

Some of them use a different numbering convention: (1 of 2) and so
forth.

I would like to trim the parentheses and numbers off of the end of
every record in which there is one. Some of the records do not have
such numbering, however, and I would like these to stay the same.

Does this help?


Matt
 
Set [Company] = Left([Company],InStr([Company],"(")-1)
Click to expand...

Thanks for your help.

I tried inserting your code so that my SQL now looks like this:

UPDATE [tbl: Data - Companies] SET [tbl: Data - Companies].Company =
Left([Company],InStr([Company],"(")-1)
WITH OWNERACCESS OPTION;

Now, when I run it, it deletes almost all of the Company names, leaving
those fields blank. None of the remaining company names have a
(number) after it, which I guess is a good sign, but you can see why
losing my company names might be a bit worse <g>.

Perhaps I should be more specific about what I am trying to accomplish.
Right now, there are a number of records that look like this:

Company1 (1)
Company1 (2)
Company1 (3)
Company2 (1)
Company2 (2)
Company2 (3)

Some of them use a different numbering convention: (1 of 2) and so
forth.

I would like to trim the parentheses and numbers off of the end of
every record in which there is one. Some of the records do not have
such numbering, however, and I would like these to stay the same.

Does this help?

Matt
Click to expand...

Re: >Some of the records do not have such numbering, however,<

Aha. You didn't state that before.

You'll need to restrict the update to just those records that do have
the parenthesis.

WHERE (((tbl: Data - Companies) Is Not Null) AND (InStr([tbl: Data -
Companies],"(")>"0"))
 
fredg said:
WHERE (((tbl: Data - Companies) Is Not Null) AND (InStr([tbl: Data -
Companies],"(")>"0"))
Click to expand...

Wow, that worked, and much more cleanly than I had hoped for :)

I'm going to have to try and learn more of these functions.


Thanks a lot!
Matt
 
Back
Top