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
Solved

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

Posted on 2014-12-09
9
251 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
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…

808 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