Find and Replace Text????

[Davidj]

I am looking for an easier way to replace a large amount of text ie A to 01,
B to 02 etc.... within two fields, currently i do this outside the datbase
within execl but i am looking for a way to code. struggling I am! could any
one help!!

 

[Ofer]

Back up your data first

You can use update query to update all the records, replacing one value with
anoter one

UPDATE TableName SET TableName.FieldName = "01"
WHERE TableName.FieldName="A"

Or if you want to replace one field value with the another field value

UPDATE TableName SET TableName.Field1Name = [Field2Name]
============================================
Again, before you try anything please back up, unlike excel, you can't press
ctrl + z and return the prev values

 

['69 Camaro]

Hi, David.

If I understand your question and you want to replace all occurrences of A
with 01, B with 02, C with 03, et ecetera, then one may use an update query
with nested Replace( ) functions. For example, try:

UPDATE tblMyTable
SET Stuff = Replace(Replace(Replace(NZ(Stuff, ""), "A", "01", 1, -1, 2),
"B", "02", 1, -1, 2), "C", "03", 1, -1, 2),
MoreStuff = Replace(Replace(Replace(NZ(MoreStuff, ""), "A", "01", 1, -1,
2), "B", "02", 1, -1, 2), "C", "03", 1, -1, 2);

.. . . where tblMyTable is the name of the table, Stuff is the name of one of
these fields, and MoreStuff is the name of the other field.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Ken Snell [MVP]]

Check out the Replace function.

 

[Duane Hookom]

If you are replace entire field contents, create a table with two text field

TextToFind primary key
TextReplace

You can create an update query based on your original table and the new
table. Join the TextToFind place to your field you want to replace. Update
your field with the TextReplace field.

 

[Tim Ferguson]

I am looking for an easier way to replace a large amount of text ie A
to 01, B to 02 etc.... within two fields, currently i do this outside
the datbase within execl but i am looking for a way to code.
struggling I am! could any one help!!

UPDATE MyTable
SET NewField = ASC(OldField) - ASC("A") + 1
WHERE OldField IS NOT NULL

Of course, if you can bothered to look up the value of ASC("A") then the
query will run very slightly faster but will be less self-documenting.

If you have a text field and the leading zero is important to you, then you
need to insert a format function:

SET NewField = FORMAT(ASC(OldField) - ASC("A") + 1,"00")

Hope that helps


Tim F

 

[Mark A]

Hi Duane. I think you are nearly answering in your response a query I am
trying to get resolved. I have a field called "category" in a Main Table.
There are about 150 different values populating this field. I want to
consolidate this list to about 30. I have created a "Consolidation" table
with two fields: "Old Category" and "New Category" which contains values such
as:

OLD VALUE NEW VALUE
Test Strategy DBT
Test Approach DBT
Test Planning DBT
Design Stage DBT

I've tried:
UPDATE [MainTable] SET [MainTable].[Category] = [ Consolidation]![New
Category]
WHERE ((([MainTable].[Category])=[ Consolidation]![Old Category]));
but to no avail (I get prompted for a parameter) - can you help? I want to
avoid building an Update statement by hand.

Cheers, Mark

 

[Duane Hookom]

The [Old Category] field must be the primary key of the Consolidation table.
Create a select query that includes both tables. Join the [Old Category]
field to the [Category] field. Change the query to an update query and set
the Update To: [Consolidation]![New Category].

Make sure you don't have spaces at the front of your table name ([
Consolidation]).

--
Duane Hookom
MS Access MVP
--

Hi Duane. I think you are nearly answering in your response a query I am
trying to get resolved. I have a field called "category" in a Main Table.
There are about 150 different values populating this field. I want to
consolidate this list to about 30. I have created a "Consolidation" table
with two fields: "Old Category" and "New Category" which contains values
such
as:

OLD VALUE NEW VALUE
Test Strategy DBT
Test Approach DBT
Test Planning DBT
Design Stage DBT

I've tried:
UPDATE [MainTable] SET [MainTable].[Category] = [ Consolidation]![New
Category]
WHERE ((([MainTable].[Category])=[ Consolidation]![Old Category]));
but to no avail (I get prompted for a parameter) - can you help? I want to
avoid building an Update statement by hand.

Cheers, Mark

If you are replace entire field contents, create a table with two text
field

TextToFind primary key
TextReplace

You can create an update query based on your original table and the new
table. Join the TextToFind place to your field you want to replace.
Update
your field with the TextReplace field.

 

[John Spencer]

Pardon me. But why not just use this update query

UPDATE [MainTable] INNER JOIN [Consolidation]
ON [Main Table].[Category] = [Consolidation].[Old Category]
SET [MainTable].[Category] = [Consolidation].[New Category]
WHERE [Consolidation].[New Category] is not null

The [Old Category] field must be the primary key of the Consolidation
table. Create a select query that includes both tables. Join the [Old
Category] field to the [Category] field. Change the query to an update
query and set the Update To: [Consolidation]![New Category].

Make sure you don't have spaces at the front of your table name ([
Consolidation]).

--
Duane Hookom
MS Access MVP
--

Hi Duane. I think you are nearly answering in your response a query I am
trying to get resolved. I have a field called "category" in a Main Table.
There are about 150 different values populating this field. I want to
consolidate this list to about 30. I have created a "Consolidation" table
with two fields: "Old Category" and "New Category" which contains values
such
as:

OLD VALUE NEW VALUE
Test Strategy DBT
Test Approach DBT
Test Planning DBT
Design Stage DBT

I've tried:
UPDATE [MainTable] SET [MainTable].[Category] = [ Consolidation]![New
Category]
WHERE ((([MainTable].[Category])=[ Consolidation]![Old Category]));
but to no avail (I get prompted for a parameter) - can you help? I want
to
avoid building an Update statement by hand.

Cheers, Mark

If you are replace entire field contents, create a table with two text
field

TextToFind primary key
TextReplace

You can create an update query based on your original table and the new
table. Join the TextToFind place to your field you want to replace.
Update
your field with the TextReplace field.
--
Duane Hookom
MS Access MVP


I am looking for an easier way to replace a large amount of text ie A
to
01,
B to 02 etc.... within two fields, currently i do this outside the
datbase
within execl but i am looking for a way to code. struggling I am!
could
any
one help!!

 

[Duane Hookom]

I thought that was the basic SQL that would have been created by my
suggestion other than the Where clause.

--
Duane Hookom
MS Access MVP

Pardon me. But why not just use this update query

UPDATE [MainTable] INNER JOIN [Consolidation]
ON [Main Table].[Category] = [Consolidation].[Old Category]
SET [MainTable].[Category] = [Consolidation].[New Category]
WHERE [Consolidation].[New Category] is not null

The [Old Category] field must be the primary key of the Consolidation
table. Create a select query that includes both tables. Join the [Old
Category] field to the [Category] field. Change the query to an update
query and set the Update To: [Consolidation]![New Category].

Make sure you don't have spaces at the front of your table name ([
Consolidation]).

--
Duane Hookom
MS Access MVP
--

Hi Duane. I think you are nearly answering in your response a query I am
trying to get resolved. I have a field called "category" in a Main
Table.
There are about 150 different values populating this field. I want to
consolidate this list to about 30. I have created a "Consolidation"
table
with two fields: "Old Category" and "New Category" which contains values
such
as:

OLD VALUE NEW VALUE
Test Strategy DBT
Test Approach DBT
Test Planning DBT
Design Stage DBT

I've tried:
UPDATE [MainTable] SET [MainTable].[Category] = [ Consolidation]![New
Category]
WHERE ((([MainTable].[Category])=[ Consolidation]![Old Category]));
but to no avail (I get prompted for a parameter) - can you help? I want
to
avoid building an Update statement by hand.

Cheers, Mark

:

If you are replace entire field contents, create a table with two text
field

TextToFind primary key
TextReplace

You can create an update query based on your original table and the new
table. Join the TextToFind place to your field you want to replace.
Update
your field with the TextReplace field.
--
Duane Hookom
MS Access MVP


I am looking for an easier way to replace a large amount of text ie A
to
01,
B to 02 etc.... within two fields, currently i do this outside the
datbase
within execl but i am looking for a way to code. struggling I am!
could
any
one help!!

 

[John Spencer]

THUMP! THUMP! THUMP! (Sound of head banging on wall)
John must write the following one hundred times.

John must carefully read Duane's responses!

I thought that was the basic SQL that would have been created by my
suggestion other than the Where clause.

--
Duane Hookom
MS Access MVP

Pardon me. But why not just use this update query

UPDATE [MainTable] INNER JOIN [Consolidation]
ON [Main Table].[Category] = [Consolidation].[Old Category]
SET [MainTable].[Category] = [Consolidation].[New Category]
WHERE [Consolidation].[New Category] is not null

The [Old Category] field must be the primary key of the Consolidation
table. Create a select query that includes both tables. Join the [Old
Category] field to the [Category] field. Change the query to an update
query and set the Update To: [Consolidation]![New Category].

Make sure you don't have spaces at the front of your table name ([
Consolidation]).

--
Duane Hookom
MS Access MVP
--

Hi Duane. I think you are nearly answering in your response a query I
am
trying to get resolved. I have a field called "category" in a Main
Table.
There are about 150 different values populating this field. I want to
consolidate this list to about 30. I have created a "Consolidation"
table
with two fields: "Old Category" and "New Category" which contains
values such
as:

OLD VALUE NEW VALUE
Test Strategy DBT
Test Approach DBT
Test Planning DBT
Design Stage DBT

I've tried:
UPDATE [MainTable] SET [MainTable].[Category] = [ Consolidation]![New
Category]
WHERE ((([MainTable].[Category])=[ Consolidation]![Old Category]));
but to no avail (I get prompted for a parameter) - can you help? I want
to
avoid building an Update statement by hand.

Cheers, Mark

:

If you are replace entire field contents, create a table with two text
field

TextToFind primary key
TextReplace

You can create an update query based on your original table and the
new
table. Join the TextToFind place to your field you want to replace.
Update
your field with the TextReplace field.
--
Duane Hookom
MS Access MVP


I am looking for an easier way to replace a large amount of text ie A
to
01,
B to 02 etc.... within two fields, currently i do this outside the
datbase
within execl but i am looking for a way to code. struggling I am!
could
any
one help!!

 

[Duane Hookom]

I find attempting to write the SQL too challenging at times so I try to walk
a user through the steps of creating their query. This hides my ignorance of
some of the basic syntax and joins ;-)

--
Duane Hookom
MS Access MVP
--

THUMP! THUMP! THUMP! (Sound of head banging on wall)
John must write the following one hundred times.

John must carefully read Duane's responses!

I thought that was the basic SQL that would have been created by my
suggestion other than the Where clause.

--
Duane Hookom
MS Access MVP

Pardon me. But why not just use this update query

UPDATE [MainTable] INNER JOIN [Consolidation]
ON [Main Table].[Category] = [Consolidation].[Old Category]
SET [MainTable].[Category] = [Consolidation].[New Category]
WHERE [Consolidation].[New Category] is not null

The [Old Category] field must be the primary key of the Consolidation
table. Create a select query that includes both tables. Join the [Old
Category] field to the [Category] field. Change the query to an update
query and set the Update To: [Consolidation]![New Category].

Make sure you don't have spaces at the front of your table name ([
Consolidation]).

--
Duane Hookom
MS Access MVP
--

Hi Duane. I think you are nearly answering in your response a query I
am
trying to get resolved. I have a field called "category" in a Main
Table.
There are about 150 different values populating this field. I want to
consolidate this list to about 30. I have created a "Consolidation"
table
with two fields: "Old Category" and "New Category" which contains
values such
as:

OLD VALUE NEW VALUE
Test Strategy DBT
Test Approach DBT
Test Planning DBT
Design Stage DBT

I've tried:
UPDATE [MainTable] SET [MainTable].[Category] = [ Consolidation]![New
Category]
WHERE ((([MainTable].[Category])=[ Consolidation]![Old Category]));
but to no avail (I get prompted for a parameter) - can you help? I
want to
avoid building an Update statement by hand.

Cheers, Mark

:

If you are replace entire field contents, create a table with two
text field

TextToFind primary key
TextReplace

You can create an update query based on your original table and the
new
table. Join the TextToFind place to your field you want to replace.
Update
your field with the TextReplace field.
--
Duane Hookom
MS Access MVP


I am looking for an easier way to replace a large amount of text ie
A to
01,
B to 02 etc.... within two fields, currently i do this outside the
datbase
within execl but i am looking for a way to code. struggling I am!
could
any
one help!!

 

[Mark A]

Thanks all, that has worked a treat!
Mark

I find attempting to write the SQL too challenging at times so I try to walk
a user through the steps of creating their query. This hides my ignorance of
some of the basic syntax and joins ;-)

--
Duane Hookom
MS Access MVP
--

THUMP! THUMP! THUMP! (Sound of head banging on wall)
John must write the following one hundred times.

John must carefully read Duane's responses!

I thought that was the basic SQL that would have been created by my
suggestion other than the Where clause.

--
Duane Hookom
MS Access MVP


Pardon me. But why not just use this update query

UPDATE [MainTable] INNER JOIN [Consolidation]
ON [Main Table].[Category] = [Consolidation].[Old Category]
SET [MainTable].[Category] = [Consolidation].[New Category]
WHERE [Consolidation].[New Category] is not null

The [Old Category] field must be the primary key of the Consolidation
table. Create a select query that includes both tables. Join the [Old
Category] field to the [Category] field. Change the query to an update
query and set the Update To: [Consolidation]![New Category].

Make sure you don't have spaces at the front of your table name ([
Consolidation]).

--
Duane Hookom
MS Access MVP
--

Hi Duane. I think you are nearly answering in your response a query I
am
trying to get resolved. I have a field called "category" in a Main
Table.
There are about 150 different values populating this field. I want to
consolidate this list to about 30. I have created a "Consolidation"
table
with two fields: "Old Category" and "New Category" which contains
values such
as:

OLD VALUE NEW VALUE
Test Strategy DBT
Test Approach DBT
Test Planning DBT
Design Stage DBT

I've tried:
UPDATE [MainTable] SET [MainTable].[Category] = [ Consolidation]![New
Category]
WHERE ((([MainTable].[Category])=[ Consolidation]![Old Category]));
but to no avail (I get prompted for a parameter) - can you help? I
want to
avoid building an Update statement by hand.

Cheers, Mark

:

If you are replace entire field contents, create a table with two
text field

TextToFind primary key
TextReplace

You can create an update query based on your original table and the
new
table. Join the TextToFind place to your field you want to replace.
Update
your field with the TextReplace field.
--
Duane Hookom
MS Access MVP


I am looking for an easier way to replace a large amount of text ie
A to
01,
B to 02 etc.... within two fields, currently i do this outside the
datbase
within execl but i am looking for a way to code. struggling I am!
could
any
one help!!