Query Version of Find and Replace

[tension22]

Hi,

I need a query that works exactly like find and replace function. I have tried many ways, but the result is always the same - the whole string gets changed, not just the character/s in need.

So if I need all "!" exclamation remarks changing to a "?" question mark it will do it.

So would change this:

Help!
Oi!
Hi!
Hello!

to

Help?
Oi?
Hi?
Hello?


Also note that I can't just do an update query to find

Help!
Oi!
Hi!
Hello!

and replace with:

Help?
Oi?
Hi?
Hello?

Its must only work on finding the ! and replacing with ?, just like find and replace.

Thanks

 

[Brianwarnock]

Use of the search facility will show that this has been asked before.

Brian

 

[FoFa]

Query using like to find it, and replace function

 

[darwin25]

create a function in Module


Function FindReplace(FindString As String, Substitute As String, StringExpr As String) As String

Dim n_Pos As Integer

While InStr(StringExpr, FindString)

n_Pos = InStr(StringExpr, FindString)
​

StringExpr = Mid(StringExpr, 1, n_Pos - Len(FindString) + 3) & Substitute & Mid(StringExpr, n_Pos + Len(FindString), Len(StringExpr) - n_Pos)
​

Wend


FindReplace = StringExpr

End Function



And use it in your query like this

FindReplace(String1,String2,Field)

where
String1 = string to Find
String2 = new value
Field = fieldname of the string expression


HTH

 

[DCrake]

Why not use the built in Replace() function?

 

[darwin25]

Why not use the built in Replace() function?

Yes. that's much simpler

 

[Galaxiom]

Why not use the built in Replace() function?

I think Darwin was just answering the question.
"I need a query that works exactly like find and replace function."

I like their subtle sense of humor.

 

[lighterthief]

I have a similar issue.

I wish to find a particular piece of text at the end of a text field, then replace only those characters (ie only appearing at the end of the field) with "".

For example, I wish to remove " UK" from the end of a text field.

"Academy of Ukelele Players UK"

would become:

"Academy of Ukelele Players"

I have tried using the function as suggested by Darwin25 but this results in both of the previous examples returning as:

"Academy ofelele Players".

How can I specify that the text should only be replaced if it appears at the end of the field? The text fields are all of varying length, so I can't use the Replace function to replace at a fixed point, although I have tried various permutations, eg

Replace([table.name]![table.field]," UK","",len([table.name]![table.field]-3))

but with no joy. I am sure there must be an elegant solution to this problem and would be grateful for any advice.

 

[Brianwarnock]

A simple function something like

If Right(yourstring,2)="UK" then
Yourstring=Left(yourstring,len(yourstring)-2)

Brian

 

[lighterthief]

Yup, thanks for that. Basically got myself stuck down a long and complicated route when in reality there was a much simpler solution staring at me all along. Appreciate the help

 

[Brianwarnock]

We've all been there, done that.

Brian