[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2014-12-09
9
Medium Priority
?
256 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 1000 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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
 

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 48

Accepted Solution

by:
Dale Fye earned 1000 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 48

Expert Comment

by:Dale Fye
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

650 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