Member Avatar for SlyMaelstrom

Hi, I'm doing some work for an Access Database but I caught a snag writing an event. I want the event to (among other things) change the Control Source of an object. This is no problem, except I have to set the control source as a string, and the string being an Immediate if function contains a string in it.

Now in C and C++, to write double quotes in a literal constant you just use a backslash infront of it to take it as it is. Such as:

str1 = "President Lincoln said \"Four score and seven years ago...\""

I tried this in my VBA statement and no such luck. It just took the backslash in a string and closed the string at the quotes, like so:

[foo].ControlSource = "=IIf([foo]>0, 100*([foo1]/[foo2]) & \"%\", \"%\")"

So does anyone know the code to accept double quotes in string constants?

  • 5 Contributors
  • 6 Replies
  • 668 Views
  • 5 Years Discussion Span
  • Latest Post Latest Post by kiran2010

Dani AI

Generated

As noted, the most readable way in VBA is to put a literal quote in a string by doubling it; as explained, constructing the string with a character function (Chr/ChrW) is the programmatic alternative. For Access ControlSource values the doubled‑quote literal is usually easiest; when text comes from external input (copy/paste, user fields) the Chr/ChrW route or a normalization step is safer.

Example (different from earlier thread examples) — building a ControlSource that shows a currency or a fallback label:

Dim cs As String
cs = "=IIf([Stock]>0, Format([Price], ""$#,##0.00""), ""Out of stock"")"
Me!txtPrice.ControlSource = cs

On the smart/curly-quote problem raised by : “curly” quotes are different characters (Unicode U+201C / U+201D, and in CP1252 they appear as bytes 147/148). If pasted text contains those, replace them with the straight ASCII quote before use. A compact helper:

Function NormalizeQuotes(s As String) As String
    s = Replace(s, ChrW(8220), Chr(34))
    s = Replace(s, ChrW(8221), Chr(34))
    s = Replace(s, Chr(147), Chr(34)) ' CP1252 left
    s = Replace(s, Chr(148), Chr(34)) ' CP1252 right
    NormalizeQuotes = s
End Function

Quick troubleshooting tips: inspect character codes with AscW/Mid to find offending bytes (loop and Debug.Print AscW(Mid$(s,i,1))). Prefer literal doubled quotes for fixed strings; normalize input that may contain smart quotes when assembling SQL/control-source or when doing exact matches.

Recommended Answers

All 6 Replies

Member Avatar for Comatose

I will say that the C/C++/Javaish/Perlish/etc/etc structure for working with escape sequences is a real treat compared to The basic language. The only way to do this, is to concantenate the character code value to the string where the double quote would be. It's sick, and it makes the code a lot less understandable when dealing with strings, but here is how your example would look:

str1 = "President Lincoln said " & chr(34) & "Four score and seven years ago..." & chr(34)

chr is the basic function to return the ascii value of a given character code. In this case, 34, which is ". This page has a cool list of them: http://www.lookuptables.com/

Member Avatar for SlyMaelstrom

Yep, that makes sense. Thank you.

Member Avatar for Ganesh_Kutty

But when i use Chr(34) it replaces a smart curly (“) double quote but i need to replace it with a straight double quote (") which. Is there a solution for this.

Member Avatar for SlyMaelstrom

Wow... here is another reason why bumping posts suck... I get a stupid email from a thread I subscribed to almost a year ago. Thanks for the spam, Ganesh.

commented: No Kidding, Ganesh is out to lunch... +5
Member Avatar for ShaunRigby

Wow... here is another reason why bumping posts suck... I get a stupid email from a thread I subscribed to almost a year ago. Thanks for the spam, Ganesh.

<bump /> :D

Member Avatar for kiran2010

Hey use double times double quote for any escape sequence character like

[foo].ControlSource = "=IIf([foo]>0, 100*([foo1]/[foo2]) & ""%"", ""%"")"

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.