Access - Variable calling a DB record field that has quotation marks in the text

Hello experts

I have a bit of code that sets a record field as a String.  Then that variable is used in a VB code to send an Email or Text message.

That records field has Quotations in the field.... SOOOO when the variable is set using a Dim strABC as string and then the code that uses it is .Subject = "Info From: " & strABC & " " & lnginfo it is interpreting the info as quotations in quotations.

Is there a way that the Dim'ed string can have quotations that the code will use?
Who is Participating?
Dale FyeConnect With a Mentor Commented:
I use a function, to encapsulate most of my text strings in quotes.  It replaces the single quotes (") inside of the text with double quotes (""), which VBA/Jet (not sure which, or maybe both) interpret as a single quote.  The function looks like:

Public Function fnQuotes(QuoteWhat as Variant, _
                         Optional QuoteWith as String = """") as String

    if isNull(QuoteWhat) then
        fnQuotes = QuoteWith & "NULL" & QuoteWith
        fnQuotes = QuoteWith _
                 & Replace(QuoteWhat, QuoteWith, QuoteWith & QuoteWith) _
                 & QuoteWith
    End If

End Function

Open in new window

Then, whenever I want to encapsulate a string in quotes, I just call this function and pass it the value.  One of the neat tricks is that you can use it to wrap text in " for Access/Jet text or # for Access/Jet date fields or with ' for SQL Server.  In your case I think this would look like:

.Subject = "Info From: " & fnQuotes(strABC) & " " & lnginfo

Another example might be:

strSQL = "SELECT * FROM yourTable WHERE [NameLast] = " & quotes(me.txt_NameLast)

This technique takes less keystrokes than the chr$(34) method:

strSQL = "SELECT * FROM yourTable " _
            & "WHERE [NameLast] = " & chr$(34) & Replace(me.txt_NameLast, chr$(34), chr$(34) & chr$(34)) & chr$(34)

and is significantly easier to read than any alternative which includes multiple "" combinations.
Nick67Connect With a Mentor Commented:
You can build or Google up various 'escape' functions for VBA
They are all of a type.
Detect the evil and substitute something for it
This is aircode

Pubiic Function FixedString(NewData as String) as String

If Not NewData Like Chr(34) Then 'Chr(34) is a quote
    FixedString = NewData
    exit function
end if
'we have a quote(s)

Dim MySplit() as String
MySplit = Split(NewData, Chr(34)) 'split it on the quote
Dim x as integer
FixedString =""
'put the string back together subbing Chr(34) for "
For x = Lbound(MySplit) to Ubound(mySplit) - 1
    FixedString = FixedString & MySplit(x) & " & Chr(34) & "
Next x
' add the last element
FixedString = FixedString & MySplit(Ubound(mySplit))
'that should do it
end function
wlwebbAuthor Commented:
Oh yuk! (LOL).... thought it would be easier......

But thank you !

I'll hold open for a bit to see if anyone has anything else .... but I'd say that will be the best.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

wlwebbAuthor Commented:

FixedString =""
is that two quotation marks or 4 single quote marks????

And what happens if the Quotations from the field are in the middle of the text.?????
FixedString = "" empty double quotes to initialize it.  Leasing & trailing qoutes would be a problem.  In the middle is the idea
wlwebbAuthor Commented:
When I run the code all I end up with is an Empty String..... even though the Quotation Marks are in the middle....??????  Thought that might be my problem.
wlwebbAuthor Commented:
Thank you Dale (I think you used to be Fyed correct???)....

I will give that one a try...
Dale FyeCommented:
Yes, still am.  But now you can go to your profile and add in an Alias that you want to use instead of your EE userid.
wlwebbAuthor Commented:
Thanks to both... Thought I had closed this before I went on the road last week
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.