update query

[rohini]

how do you use update query to change a part of the data in any cell in a table.



for example:
i want to change 'i love mangoes' to 'i love apple' i.e. i am changing a part of text

[June7]

Can run Find/Replace dialog on the table or try:

UPDATE table SET fieldname = Replace(fieldname, 'mangoes', 'apples')

Use the query designer to build and save query object then use code to run. If you want to automate this with user input for the text change, how do you want users to provide the inputs? By query input parameters or by reference to controls on a form?

The SQL statement can also be built and run entirely with VBA code and executed with a button Click event, like:

CurrentDb.Execute "UPDATE table SET fieldname = Replace(fieldname, '" & Me.textboxOldText & "', '" & Me.textboxNewText & "'")

[rohini]

what is basically the case is that i have a table
one column with names of people and other with "i love mangoes", "she loves mangoes", "he loves mangoes", etc
where all of this has to be changed to ""i love apples", "she loves apples", "he loves apples", etc
dint really get what you wrote above..

[rohini]

hey

the input table is:

name	fruit
joey	joey likes mangoes
rachel	rachel likes mangoes
monica	monica likes mangoes
ross	ross likes pineapple
emily	emily likes mangoes

output should be:

name	fruit
joey	joey likes apples
rachel	rachel likes apples
monica	monica likes apples
ross	ross likes pineapple
emily	emily likes apples

hope that clarifies everything!

[June7]

Why are you duplicate saving the name and 'likes' anyway? Just save the fruit and then build the sentence with an expression in a textbox whenever needed.

My suggestion is to:

1. build a form where the 'old' and 'new' values can be input into textboxes

2. build an UPDATE query that refers to the textboxes as parameters

3. run the UPDATE query either as an Access query object or with the VBA command I show

Building the query with designer is basic Access functionality.