?
Solved

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

Posted on 2014-12-09
9
Medium Priority
?
254 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

801 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