Replace and update query in vba

[ilanray]

Hi
I would like to update a query with special characters like '
the update statement looks loke "update Task set titles = '" & titles & " ' where....."
The problem is if I am using

 "update Task set titles = '" & replace (titles,"'","''") & " ' where....."

is save in the database twice of the ' characters for example I the titles= "isn't" it will save it as "isn''t"
How can I save it as "isn't"

Tthanks

 

[isladogs]

It might help if you had posted the entire query SQL
Use the same approach - double up the single quotes in a Replace statement
Possibly save the string as a variable?

If necessary, run two update queries in turn.

 

[ebs17]

sSQL = "update Task set titles = replace(titles, ''', '''') where ..."
' or
sSQL = "update Task set titles = replace(titles, Chr(39), Chr(39) & Chr(39)) where ..."

Replace with the table field can be processed directly by Jet/ACE. Therefore, there is no need to separate the expression from the SQL string and no such unspeakable and error-prone extra formatting.
But I wonder what the measure is supposed to be good for.

At the top you had also added some free spaces that shouldn't be there.

 

[ilanray]

the spaces aren't suppose to be there I just want to Emphasize the ' character which is double
if I will add the chr(39) and replace it with chr(39)&chr(39) it still have twice of the '

 

[ebs17]

Clearly, the measure shown performs a doubling.
To eliminate:

sSQL = "update Task set titles = replace(titles, Chr(39), '') where ..."

 

[cheekybuddha]

If you build SQL strings in VBA code, then create a helper function like the one in this post, or you can also use a dedicated class like the one in this post (though it gets more complex that way).

So your code would look like:

strSQL = "UPDATE Task SET titles = " & SQLStr(Me.titles) & " WHERE ....."

If you are just trying to do it within a query, then you can omit the surrounding quotes by passing the Wrap parameter as false:

UPDATE Task SET titles = SQLStr(titles, False) WHERE ..... ;

 

[ilanray]

Well I used the SQLStr function as you describe

title = " & SQLstr(me.title,false) & " where..."

and still the apostrophe is double

 

[cheekybuddha]

OK, I'm not quite clear how your data is at the moment and how you want your data to look like after.

So do you now have a title like (option 1):
This isn''t right
and you want:
This isn't right

Or do you have (option 2):
This isn't right
and you want:
This isn''t right

 

[ilanray]

option 1 i want it to be

This isn't right

 

[cheekybuddha]

OK, this should be a one-off operation.

Use the following function:

Function RemoveDoubleSingleQuotes() As Boolean

  Dim strSQL As String, iCount As Integer
 
  iCount = DCount("*", "Task", "title LIKE '*" & Chr(39) & Chr(39) & "*'")
  Do While iCount > 0
    CurrentDb.Execute("UPDATE Task SET titles = Replace(titles, '" & Chr(39) & Chr(39) & "', '" & Chr(39) & "')")
    iCount = DCount("*", "Task", "title LIKE '*" & Chr(39) & Chr(39) & "*'")
  Loop
  RemoveDoubleSingleQuotes = (Err = 0)

End Function

Call it from the Immediate Window (Ctrl+G)

?RemoveDoubleSingleQuotes

(NB Untested!)

 

[cheekybuddha]

Ooops! Sorry, I should have tested

Try it like this:

Function RemoveDoubleSingleQuotes() As Boolean

  Dim strSQL As String, iCount As Integer
 
  iCount = DCount("*", "Task", "title LIKE '*''''*'")
  Do While iCount > 0
    CurrentDb.Execute "UPDATE Task SET titles = Replace(titles, Chr(39) & Chr(39), Chr(39))", dbFailOnError
    iCount = DCount("*", "Task", "title LIKE '*''''*'")
  Loop
  RemoveDoubleSingleQuotes = (Err = 0)

End Function

 

[ilanray]

Well I tried that' it doesn't work well. it gives me an erroe message for the update query about the icount
can you please send me you db that you created? I would like to see how ir works for you
regards

 

[cheekybuddha]

Hi @ilanray,

You give us very little information. Please remember that we can not see your screen.

What was the error message that you got when you tried the code?

You get the error when trying the code posted in Post #11 ?

I'll post the test db when I get back to machine it's on later.

 

[ilanray]

Ok I got it
the only thing I added is
iCount = DCount("*", "Task", "title LIKE '*''''*' and task_id=" & me.id)
but I forgot to add it inside the loop. now it is working thanks

 

[cheekybuddha]

Hi,

I'm glad you got it sorted.

Sorry I didn't post the test db yet.

I had done my original testing in an existing db using the code I posted in Post #11.

But when I created a new db to post here, just containing the relevant stuff, then the LIKE clause in the DCount() expression did not find any results.

So, I tweaked the WHERE expression to use InStr() instead.

Still, I can not understand why the original code works in one db and not in another!

*** UPDATE ***
OK, I discovered that I had the setting for new databases to use ANSI 92 SQL syntax for some reason

Attaching the test db here: