Apostrophes in search strings

[Michael T]

I have an SQL statement which I build as follows:

strSQL = "UPDATE tblDesign " & _
"SET " & strFieldNameForUpdate & " = '" & strDM & "' " & _
"WHERE DesignName = '" & strName & "' ;"

However, in some instances there is an apostrophe in the data in strName
(e.g. King's Dinner) and therefore this code builds the string:

UPDATE tblDesign SET DesignMonarchSide = 'R' WHERE DesignName = 'King's
Dinner' ;

I cannot eliminate the apostrophe because it is in the data in the table we
are updating (I know it's not the nicest way of updating but this row is the
key and I am stuck with it).

I have considered searching for the apostrophe and splitting the string into
two with an apostrophe between both (eg ending up with something like
"'King" & "'" & "s Dinner") but this gets incredibly complicated.

Is there an easy way to get round this?

Thanks,

Michael.

 

[fredg]

I have an SQL statement which I build as follows:

strSQL = "UPDATE tblDesign " & _
"SET " & strFieldNameForUpdate & " = '" & strDM & "' " & _
"WHERE DesignName = '" & strName & "' ;"

However, in some instances there is an apostrophe in the data in strName
(e.g. King's Dinner) and therefore this code builds the string:

UPDATE tblDesign SET DesignMonarchSide = 'R' WHERE DesignName = 'King's
Dinner' ;

I cannot eliminate the apostrophe because it is in the data in the table we
are updating (I know it's not the nicest way of updating but this row is the
key and I am stuck with it).

I have considered searching for the apostrophe and splitting the string into
two with an apostrophe between both (eg ending up with something like
"'King" & "'" & "s Dinner") but this gets incredibly complicated.

Is there an easy way to get round this?

Thanks,

Michael.

Use 2 " instead of 1 '.....
"WHERE DesignName = " " " & strName & " " " ;"

The spaces within the quotes are just for clarity. Remove the spaces.

 

[NoodNutt]

Michael

Try this

WHERE DesignName = " & Chr(34) & strName & Chr(34) & ";"

Chr(34) will handle the ' in names

HTH
Mark.

 

[Michael T]

Thank you - excellent solution.

 

[Michael T]

Thank you, excellent solution.

Use 2 " instead of 1 '.....
"WHERE DesignName = " " " & strName & " " " ;"

The spaces within the quotes are just for clarity. Remove the spaces.

 

[Dale Fye]

Michael,

The way I handle this is that I have created a function that I use when
creating SQL strings dynamically.

Public Function Quotes(TextToQuote as Variant, _
Optional WrapWith as string = """") as string

'accepts a variant to handle NULLs and returns an empty string
'when a null is encountered

'If the TextToQuote value contains the WrapWith character
'embedded in the string, then the Replace function replaces a
'single occurance of that value with two of it.
Quotes = WrapWith _
& Replace(NZ(TextToQuote, ""), WrapWith, WrapWith & WrapWith) _
& WrapWith

End Function

You would need to put this in a code module, and then use it similar to:

strSQL = "UPDATE tblDesign" _
& " SET [" & strFieldNameForUpdate & "] = " & Quotes(strDM) _
& " WHERE [DesignName] = " & Quotes(strName)

This will avoid the problem with single quotes all together, and if one of
your strings happens to contain a double quote, it will automatically replace
it with two double quotes.

One of the nice things about this is that it works with dates as well, by
passing the pound sign (#) as the second parameter.

HTH
Dale