Solved

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

Posted on 2014-12-09
9
249 Views
Last Modified: 2014-12-23
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.

HOWEVER
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?
0
Comment
Question by:wlwebb
  • 5
  • 2
  • 2
9 Comments
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 250 total points
ID: 40490295
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
0
 

Author Comment

by:wlwebb
ID: 40490319
Nick....
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.
0
 

Author Comment

by:wlwebb
ID: 40490337
Nick

Is
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.?????
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40490356
FixedString = "" empty double quotes to initialize it.  Leasing & trailing qoutes would be a problem.  In the middle is the idea
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:wlwebb
ID: 40490365
Hmmm....
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.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 40490376
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
    Else
        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.
0
 

Author Comment

by:wlwebb
ID: 40490385
Thank you Dale (I think you used to be Fyed correct???)....


I will give that one a try...
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40490391
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.
0
 

Author Closing Comment

by:wlwebb
ID: 40514676
Thanks to both... Thought I had closed this before I went on the road last week
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now