Change Text Data in a Field/Table

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

If my table has a field called [Fruit] and in that field I have 500 records,
160 of them Have the word "Apples" how can I change "Apples" to "Green
Apples" with out having to scroll through the 500 records on a form
 
You can use an Update query.

The SQL will be something like:

Update MyTable SET Fruit = Replace([Fruit], "Apples", "Green Apples")
WHERE Fruit LIKE "*Apples*"
 
Thanks Douglas , that was just and example, is it possible to have a query
(No Duplicates) on that field and change Apples to green apples?
or Pears to peaches or anything?....Thanks Bob
Douglas J. Steele said:
You can use an Update query.

The SQL will be something like:

Update MyTable SET Fruit = Replace([Fruit], "Apples", "Green Apples")
WHERE Fruit LIKE "*Apples*"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bob Vance said:
If my table has a field called [Fruit] and in that field I have 500
records,
160 of them Have the word "Apples" how can I change "Apples" to "Green
Apples" with out having to scroll through the 500 records on a form
Click to expand...
Click to expand...
 
If you've only got a few, you can chain the Replace function calls:

Update MyTable
SET Fruit = Replace(Replace(Replace([Fruit], "Apples", "Green Apples"),
"Pears", "Peaches"), "Grapes", "Red Grapes")

If you've got a long list and Fruit only contains the single word or phrase
(i.e.: it's not a sentence like "I like apples, watermelon and lemons."),
you can create a cross reference table that contains the "From" and "To"
words, then you can try something like:

UPDATE MyTable INNER JOIN XRef
ON MyTable.Fruit = XRef.FruitFrom
SET Fruit = XRef.FruitTo

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bob Vance said:
Thanks Douglas , that was just and example, is it possible to have a query
(No Duplicates) on that field and change Apples to green apples?
or Pears to peaches or anything?....Thanks Bob
Douglas J. Steele said:
You can use an Update query.

The SQL will be something like:

Update MyTable SET Fruit = Replace([Fruit], "Apples", "Green Apples")
WHERE Fruit LIKE "*Apples*"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bob Vance said:
If my table has a field called [Fruit] and in that field I have 500
records,
160 of them Have the word "Apples" how can I change "Apples" to "Green
Apples" with out having to scroll through the 500 records on a form
Click to expand...
Click to expand...
Click to expand...
 
Thanks Douglas,
The actual Table was TblRemarks.Category , just a bit lost with the
XRef!......Regards Bob

UPDATE TblRemarks INNER JOIN XRef ON TblRemarks.Category=XRef.FruitFrom SET
Category = XRef.FruitTo;

Douglas J. Steele said:
If you've only got a few, you can chain the Replace function calls:

Update MyTable
SET Fruit = Replace(Replace(Replace([Fruit], "Apples", "Green Apples"),
"Pears", "Peaches"), "Grapes", "Red Grapes")

If you've got a long list and Fruit only contains the single word or
phrase (i.e.: it's not a sentence like "I like apples, watermelon and
lemons."), you can create a cross reference table that contains the "From"
and "To" words, then you can try something like:

UPDATE MyTable INNER JOIN XRef
ON MyTable.Fruit = XRef.FruitFrom
SET Fruit = XRef.FruitTo

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bob Vance said:
Thanks Douglas , that was just and example, is it possible to have a
query (No Duplicates) on that field and change Apples to green apples?
or Pears to peaches or anything?....Thanks Bob
Douglas J. Steele said:
You can use an Update query.

The SQL will be something like:

Update MyTable SET Fruit = Replace([Fruit], "Apples", "Green Apples")
WHERE Fruit LIKE "*Apples*"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


If my table has a field called [Fruit] and in that field I have 500
records,
160 of them Have the word "Apples" how can I change "Apples" to "Green
Apples" with out having to scroll through the 500 records on a form
Click to expand...
Click to expand...
Click to expand...
 
I was suggesting that you'd have a crossreference table XRef with fields
FruitFrom and FruitTo:

FruitFrom FruitTo
Apples Green Apples
Pears Peaches
Grapes Red Grapes

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bob Vance said:
Thanks Douglas,
The actual Table was TblRemarks.Category , just a bit lost with the
XRef!......Regards Bob

UPDATE TblRemarks INNER JOIN XRef ON TblRemarks.Category=XRef.FruitFrom
SET Category = XRef.FruitTo;

Douglas J. Steele said:
If you've only got a few, you can chain the Replace function calls:

Update MyTable
SET Fruit = Replace(Replace(Replace([Fruit], "Apples", "Green Apples"),
"Pears", "Peaches"), "Grapes", "Red Grapes")

If you've got a long list and Fruit only contains the single word or
phrase (i.e.: it's not a sentence like "I like apples, watermelon and
lemons."), you can create a cross reference table that contains the
"From" and "To" words, then you can try something like:

UPDATE MyTable INNER JOIN XRef
ON MyTable.Fruit = XRef.FruitFrom
SET Fruit = XRef.FruitTo

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bob Vance said:
Thanks Douglas , that was just and example, is it possible to have a
query (No Duplicates) on that field and change Apples to green apples?
or Pears to peaches or anything?....Thanks Bob
You can use an Update query.

The SQL will be something like:

Update MyTable SET Fruit = Replace([Fruit], "Apples", "Green Apples")
WHERE Fruit LIKE "*Apples*"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


If my table has a field called [Fruit] and in that field I have 500
records,
160 of them Have the word "Apples" how can I change "Apples" to
"Green
Apples" with out having to scroll through the 500 records on a form
Click to expand...
Click to expand...
Click to expand...
 
Thanks Douglas ,can you point me in the right direction for me to create a
XRef......Thanks Bob

Douglas J. Steele said:
I was suggesting that you'd have a crossreference table XRef with fields
FruitFrom and FruitTo:

FruitFrom FruitTo
Apples Green Apples
Pears Peaches
Grapes Red Grapes

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bob Vance said:
Thanks Douglas,
The actual Table was TblRemarks.Category , just a bit lost with the
XRef!......Regards Bob

UPDATE TblRemarks INNER JOIN XRef ON TblRemarks.Category=XRef.FruitFrom
SET Category = XRef.FruitTo;

Douglas J. Steele said:
If you've only got a few, you can chain the Replace function calls:

Update MyTable
SET Fruit = Replace(Replace(Replace([Fruit], "Apples", "Green Apples"),
"Pears", "Peaches"), "Grapes", "Red Grapes")

If you've got a long list and Fruit only contains the single word or
phrase (i.e.: it's not a sentence like "I like apples, watermelon and
lemons."), you can create a cross reference table that contains the
"From" and "To" words, then you can try something like:

UPDATE MyTable INNER JOIN XRef
ON MyTable.Fruit = XRef.FruitFrom
SET Fruit = XRef.FruitTo

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks Douglas , that was just and example, is it possible to have a
query (No Duplicates) on that field and change Apples to green apples?
or Pears to peaches or anything?....Thanks Bob
message You can use an Update query.

The SQL will be something like:

Update MyTable SET Fruit = Replace([Fruit], "Apples", "Green Apples")
WHERE Fruit LIKE "*Apples*"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


If my table has a field called [Fruit] and in that field I have 500
records,
160 of them Have the word "Apples" how can I change "Apples" to
"Green
Apples" with out having to scroll through the 500 records on a form
Click to expand...
Click to expand...
Click to expand...
 
Thanks Douglas found it easier to have a control button for Find And
Replace, figured that was the easiest.....Regards Bob
Bob Vance said:
Thanks Douglas ,can you point me in the right direction for me to create a
XRef......Thanks Bob

Douglas J. Steele said:
I was suggesting that you'd have a crossreference table XRef with fields
FruitFrom and FruitTo:

FruitFrom FruitTo
Apples Green Apples
Pears Peaches
Grapes Red Grapes

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bob Vance said:
Thanks Douglas,
The actual Table was TblRemarks.Category , just a bit lost with the
XRef!......Regards Bob

UPDATE TblRemarks INNER JOIN XRef ON TblRemarks.Category=XRef.FruitFrom
SET Category = XRef.FruitTo;

If you've only got a few, you can chain the Replace function calls:

Update MyTable
SET Fruit = Replace(Replace(Replace([Fruit], "Apples", "Green Apples"),
"Pears", "Peaches"), "Grapes", "Red Grapes")

If you've got a long list and Fruit only contains the single word or
phrase (i.e.: it's not a sentence like "I like apples, watermelon and
lemons."), you can create a cross reference table that contains the
"From" and "To" words, then you can try something like:

UPDATE MyTable INNER JOIN XRef
ON MyTable.Fruit = XRef.FruitFrom
SET Fruit = XRef.FruitTo

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks Douglas , that was just and example, is it possible to have a
query (No Duplicates) on that field and change Apples to green apples?
or Pears to peaches or anything?....Thanks Bob
message You can use an Update query.

The SQL will be something like:

Update MyTable SET Fruit = Replace([Fruit], "Apples", "Green Apples")
WHERE Fruit LIKE "*Apples*"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


If my table has a field called [Fruit] and in that field I have 500
records,
160 of them Have the word "Apples" how can I change "Apples" to
"Green
Apples" with out having to scroll through the 500 records on a form
Click to expand...
Click to expand...
Click to expand...
 
How hard is it to build a two column table and populate it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bob Vance said:
Thanks Douglas found it easier to have a control button for Find And
Replace, figured that was the easiest.....Regards Bob
Bob Vance said:
Thanks Douglas ,can you point me in the right direction for me to create
a XRef......Thanks Bob

Douglas J. Steele said:
I was suggesting that you'd have a crossreference table XRef with fields
FruitFrom and FruitTo:

FruitFrom FruitTo
Apples Green Apples
Pears Peaches
Grapes Red Grapes

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Douglas,
The actual Table was TblRemarks.Category , just a bit lost with the
XRef!......Regards Bob

UPDATE TblRemarks INNER JOIN XRef ON TblRemarks.Category=XRef.FruitFrom
SET Category = XRef.FruitTo;

message If you've only got a few, you can chain the Replace function calls:

Update MyTable
SET Fruit = Replace(Replace(Replace([Fruit], "Apples", "Green
Apples"), "Pears", "Peaches"), "Grapes", "Red Grapes")

If you've got a long list and Fruit only contains the single word or
phrase (i.e.: it's not a sentence like "I like apples, watermelon and
lemons."), you can create a cross reference table that contains the
"From" and "To" words, then you can try something like:

UPDATE MyTable INNER JOIN XRef
ON MyTable.Fruit = XRef.FruitFrom
SET Fruit = XRef.FruitTo

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks Douglas , that was just and example, is it possible to have a
query (No Duplicates) on that field and change Apples to green
apples?
or Pears to peaches or anything?....Thanks Bob
message You can use an Update query.

The SQL will be something like:

Update MyTable SET Fruit = Replace([Fruit], "Apples", "Green
Apples")
WHERE Fruit LIKE "*Apples*"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


If my table has a field called [Fruit] and in that field I have 500
records,
160 of them Have the word "Apples" how can I change "Apples" to
"Green
Apples" with out having to scroll through the 500 records on a form
Click to expand...
Click to expand...
Click to expand...
 
Back
Top