Double Quote in a "Text"

[Bob Barnes]

I'm looking at a DB written by someone else where a value (Text) for a
fieldname is 5'10".

I have a SQL string in a module looping thru a recordset which includes...

WHERE Fieldname = """ & R & """...where R is the String assigned to the
Fieldname...here R = 5'10"

As I recall, the 10" double-quote above cannot be included in coding like
this..in other words, just enter 5 10 in the Fieldname...my Apps exclude a
double-quote from being entered in the Field.

Is there a workaround if the user insists on 5'10" as an entry?

TIA - Bob

 

[Bob Barnes]

Use Chr(34)...

And Model = " & Chr(34) & U & Chr(34) & ";"

From June 2002 nEWSGROUPS..

If a string contains a single quote, put it inside double quotes.

If a string contains a double quote, put it inside single quotes.

If is string contains both, you can use the Chr() function to generate
whichever kind of quote you used outside, like:

this is the " in a string

can be represented with:

"this is the " & Chr(34) & " in a string"

 

[Tom Ventouris]

Try: R=""5'10""" Open with two and close with three dbl quotes - one to
represent inches.

 

[Bob Barnes]

Apparently, a double-quote within the string needs Chr(39) before-and-after
the variable in the SQL statement...and...

...a single-quote within the string needs Chr(34) before-and-after the
variable in the SQL statement.

Others reading this..is that correct, and what if BOTH a single-quote and a
double-quote are within the string?

 

[Pieter Wijnen]

Sure
Always Replace single quotes with 2 single quotes & double quotes with 2
double quotes

ie

Public Function SafeString(ByVal Txt As String) As String
SafeString=Replace(Replace(txt,Chr(39),Chr(39)&Chr(39)),Chr(34),Chr(34) &
Chr(34))
End Function

Pieter

 

[Bob Barnes]

Pieter - Thank you - Bob

Sure
Always Replace single quotes with 2 single quotes & double quotes with 2
double quotes

ie

Public Function SafeString(ByVal Txt As String) As String
SafeString=Replace(Replace(txt,Chr(39),Chr(39)&Chr(39)),Chr(34),Chr(34) &
Chr(34))
End Function

Pieter

 

[Bob Barnes]

Tom - Thank you - Bob

Try: R=""5'10""" Open with two and close with three dbl quotes - one to
represent inches.

 

[Douglas J. Steele]

Actually, I'm not sure that's correct, Pieter.

When working with strings in queries, you can usually use either ' or " as a
delimiter. When you use a single quote as the delimiter, you need to double
the single quotes in the text, but not the double quotes, When you use a
double quote as the delimiter, the opposite is true (you double the double
quotes, but not the single quotes).

I wrote about this in my May, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Pieter Wijnen"

 

[Pieter Wijnen]

Too true.

The idea holds though, You need to double any occurence of the String
delimiter used within the string
I therefore always
1) Use single quotes as a delimiter (SQLServer & Oracle compliance +
readability as a bonus)
2) Use Parameterized Queries or recordset's for updates

Pieter