Replace double quotes

[Kay]

Hello,

I am trying Replace Double Quote in code ( ") with wild card . It throws Runtime error '3075' -- Missing operator in query expression.

Dim strComment As String
Dim stra As String
Dim strb As String
Dim strAE As String
Dim strBE As String
stra = "'"
strb = """"
str1AE = "`"
str2BE = "`"



strComment = [Forms]![Test Form1A]![Comment] & ""
strComment = Replace(strComment, a, strAE)
strComment = Replace(strComment, strb, strBE)


Plz advise.

Thanks

[RayMilhon]

Not exactly sure what your trying to accomplish but lets look at your code line by line

The 4 Dim functions look fine
Dim strComment As String
Dim stra As String
Dim strb As String
Dim strAE As String
Dim strBE As String

stra = "'"
stra now has the value '
strb = """"
strb now has the value "
str1AE = "`"
str1AE now has the vale `
str2BE = "`"
str2BE now has the value ` which is exactly the same as str1AE get rid of this statement it's pointless

Ok now lets look at each statement below

strComment = [Forms]![Test Form1A]![Comment] & ""
the & "" is totally useless it doesn't do anything at all Revise this to
StrComment = [Forms]![Test Form1A]![Comment]
the results will be exactly the same

strComment = Replace(strComment, a, strAE)
I'm going to presume you mean stra not a If this is your actual code and you have option explicit the variable doesn't exist.
This statement takes the value in strcomment and replaces all ' values with ` values


strComment = Replace(strComment, strb, strBE)
This statement takes all " and replaces them with ` change the strBE to strAE you get the same result

Now as to your error message. without knowing the context of this code I can't answer that. Is the code a function or a procedure or one of the events of a control on a report or form? Your setting the value of a variable within the code but that doesn't go anywhere so it's a waste. You really need to give us a better idea of what your trying to do to get better help

[ssanfu]

Also, I noticed:

str1AE is not defined
str2BE is not defined
a is not defined


Do you have These two lines at the top of every code page?

Option Compare Database
Option Explicit

[June7]

Yes, variables declared are not the variables used.

I don't see a wildcard character in that code. What is ` character? Appears to be Chr(96) whereas apostrophe is Chr(39). I have known this character to cause issue when accidentally used (by copy/paste of code sample) instead of regular apostrophe.

[Kay]

Thank you for your quick responses.

Yes you are right a is actually stra and b is strab.

By replacing stra with StrAE I was able to convert the value with single quote such as Kay' to Kay`.

The user input this value in the Test Form which will be stored in SQL Table.

I was not able to do so for Double Quote value such as Kay" to Kay`.

The real issue is I was n't able to store data in the backend which is MS SQL.

[June7]

Try:

strComment = Replace(strComment, Chr(34), strAE)

[Kay]

Nope.. It throws same error again as below.

[June7]

I don't use SQL BE, just Access.

When I want to allow a literal apostrophe in a value of an SQL statement I use:

Replace(strSampledFrom, "'", "''")

Never tried to Replace quote mark.

I don't know what that other character is that you are trying to use. Just did this quick test in VBA immediate window and it worked:

?Replace("KJ""KJ",Chr(34),Chr(96))

However, this fails:
?Replace("KJ"KJ",Chr(34),Chr(96))

Which is why I instruct my users not to put " in any data - verboten!

[Kay]

Thanks for trying to help me ..

When i am trying to debug the issue this is where it fails :db.Execute strsql, dbFailOnError

[June7]

Yes, the SQL is choking on the extra quote mark. I edited my previous post, perhaps after you first read it.

[Kay]

Thanks June7! still haven't figured out how I can convert it.

[ssanfu]

Aha!
For single quotes (ex: O'Brian) I use this function to double up on the single quote:

Function ConvertQuotesSingle(InputVal)
   ConvertQuotesSingle = Replace(InputVal, "'", "''")
End Function

Expanded, it looks like this:
= Replace(InputVal, " ' ", " ' ' ")


In the SQL, useage is:

<snip>
...... & ConvertQuotesSingle(ubLast) & "', '" & ConvertQuotesSingle(ubFirst) & "', '"
<snip>

[June7]

I have tried and gave up and that's why I forbid use of " mark in data. My users will catch hell from me if my SQL fails because they disobeyed. I don't like apostrophes either but can deal with them.

[ssanfu]

Agreed! I use the replace function and remove the (") mark before the data is saved. But have to deal with apostrophes in names and addresses.

[June7]

How do you make the Replace function work for quote mark? As I show in earlier post I can't get that to work. This is also the OP's issue.