Solved

Handling double quotes in a string

Posted on 2012-04-11
12
508 Views
Last Modified: 2012-08-13
I have a chunk of code that needs to be able to handle a variety of text strings. The code below is part of a larger UPDATE statement...

"icd_desc = " & IIf(IsNull(rec!icd_desc), "NULL", """" & TStr(rec!icd_desc) & """") & ", "

Open in new window


In the situation that's causing my error, this is the value of rec!icd_desc:

"VENTILATION" PNEUMONITIS

With the code above, I end up with this:

""VENTILATION" PNEUMONITIS"

How do I get the quotes right in this situation? The code needs to handle a text string in rec!icd_desc with no quotes, double quotes, or single quotes.  We might get values of rec!icd_desc with no quotes:

DIABETES

or with a single quote, like this:

BURKITT'S LYMPHOMA




Thanks!!
0
Comment
Question by:L_Malchiodi
[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
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37833703
use chr(34) as wrapper

"icd_desc = " & IIf(IsNull(rec!icd_desc), "NULL", " & chr(34) &  TStr(rec!icd_desc) & chr(34) & ") & ", "
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37833756
Or you could try my Quotes function (see below) with a syntax like:

"icd_desc = " & Quotes(NZ(rec!icd_desc, "NULL"))

The function will automatically replace the double quote character in the string with a doublet ""

Public Function Quotes(varTextToQuote As Variant, Optional WrapWith As String = """") As String

   Quotes = WrapWith & Replace(Nz(varTextToQuote, ""), WrapWith, WrapWith & WrapWith) & WrapWith
   
End Function
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 37833843
As @capricorn1 has stated, I use Chr(34) --> which is the double quote character, extensively
Chr(39) is the single string character

"icd_desc = " & IIf(IsNull(rec!icd_desc), "NULL", """" & TStr(rec!icd_desc) & """") & ", "
is awfully ugly to look at  and debug.
Whenever I need to make sure that a string is enclosed in quotes I use
Chr(34) & "The string that "Must" be enclosed in quotes" & Chr(34)

Much more readable

"""" 
Is this 4 double quotes?
or 2 double quotes and 4 single quotes?
or 8 single quotes?

You may also be making things harder on yourself that you need to
Unless you updating myriad records at the same time, this is much easier to construct and debug

dim rs as recordset
set rs = currentdb.openrecordset("Select SomeField1, somefield2 from sometable where Something = somthingElse;", dbopendynaset)

with rs
    .movefirst
    do until .eof
        .edit
        !SomeField = SomeStringYouHaveConstructed
        .update
        .movenext
    loop
end with

You can thow a breakpoint on .Update and SEE what you are going to do BEFORE you do it.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:L_Malchiodi
ID: 37834248
I tried the Chr(34), it's still blowing up with the same error on this particular record. Here's the whole statement:

        strSQL = "UPDATE dbo_icd " _
            & "SET icd_release = " & IIf(IsNull(rec!icd_release), "NULL", "'" & TStr(rec!icd_release) & "'") & ", " _
            & "icd_type = " & IIf(IsNull(rec!icd_type), "NULL", "'" & TStr(rec!icd_type) & "'") & ", " _
            & "icd_code = '" & TStr(rec!icd_code) & "', " _
            & "icd_desc = " & IIf(IsNull(rec!icd_desc), "NULL", Chr(34) & TStr(rec!icd_desc) & Chr(34)) & ", " _
            & "modify_flag = " & IIf(IsNull(rec!modify_flag), "NULL", "'" & TStr(rec!modify_flag) & "'") & ", " _
            & "start_age = " & IIf(IsNull(rec!start_age), "NULL", "'" & TStr(rec!start_age) & "'") & ", " _
            & "stop_age = " & IIf(IsNull(rec!stop_age), "NULL", "'" & TStr(rec!stop_age) & "'") & ", " _
            & "icd_sex = " & IIf(IsNull(rec!icd_sex), "NULL", "'" & TStr(rec!icd_sex) & "'") & ", " _
            & "avail_mne = " & IIf(IsNull(rec!avail_mne), "NULL", "'" & TStr(rec!avail_mne) & "'") & " " _
            & "WHERE icd_itn = " & rec!icd_itn & ";"
            

Open in new window



On the offending record, it produces this:

UPDATE dbo_icd SET icd_release = 'I9', icd_type = 'D', icd_code = '495.7', icd_desc = ""VENTILATION" PNEUMONITIS", modify_flag = NULL, start_age = NULL, stop_age = NULL, icd_sex = NULL, avail_mne = 'ACTV' WHERE icd_itn = 7281;
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37834328
Two issues:

1.  Do you really want the age fields enclosed in quotes?
2.  Like I said above, use my function, it is much cleaner and easier to read.  This assumes that you do want the ages enclosed in quotes.

strSQL = "UPDATE dbo_icd " _
           & "SET icd_release = " & Quotes(NZ(rec!icd_release "NULL")) & ", " _
           & "icd_type = " & Quotes(NZ(rec!icd_type, "NULL")) & ", " _
           & "icd_code = " & Quotes(rec!icd_code) & ", " _
           & "icd_desc = " & Quotes(NZ(rec!icd_desc, "NULL")) & ", " _
           & "modify_flag = " & Quotes(NZ(rec!modify_flag, "NULL")) & ", " _
           & "start_age = " & Quotes(NZ(rec!start_age, "NULL")) & ", " _
           & "stop_age = " & Quotes(NZ(rec!stop_age, "NULL")) & ", " _
           & "icd_sex = " & Quotes(NZ(rec!icd_sex, "NULL")) & ", " _
           & "avail_mne = " & Quotes(NZ(rec!avail_mne, "NULL")) & " " _
           & "WHERE icd_itn = " & rec!icd_itn & ";"

If you don't want the ages enclosed in quotes, use:

strSQL = "UPDATE dbo_icd " _
           & "SET icd_release = " & Quotes(NZ(rec!icd_release "NULL")) & ", " _
           & "icd_type = " & Quotes(NZ(rec!icd_type, "NULL")) & ", " _
           & "icd_code = " & Quotes(rec!icd_code) & ", " _
           & "icd_desc = " & Quotes(NZ(rec!icd_desc, "NULL")) & ", " _
           & "modify_flag = " & Quotes(NZ(rec!modify_flag, "NULL")) & ", " _
           & "start_age = " & NZ(rec!start_age, "NULL") & ", " _
           & "stop_age = " & NZ(rec!stop_age, "NULL") & ", " _
           & "icd_sex = " & Quotes(NZ(rec!icd_sex, "NULL")) & ", " _
           & "avail_mne = " & Quotes(NZ(rec!avail_mne, "NULL")) & " " _
           & "WHERE icd_itn = " & rec!icd_itn & ";"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37834345
you missed " & before chr(34)

change
  & "icd_desc = " & IIf(IsNull(rec!icd_desc), "NULL", Chr(34) & TStr(rec!icd_desc) & Chr(34)) & ",

with

  & "icd_desc = " & IIf(IsNull(rec!icd_desc), "NULL", " & Chr(34) & TStr(rec!icd_desc) & Chr(34)) & ",
0
 

Author Comment

by:L_Malchiodi
ID: 37834379
This:

& "icd_desc = " & IIf(IsNull(rec!icd_desc), "NULL", " & Chr(34) & TStr(rec!icd_desc) & Chr(34)) & ", " _

Open in new window


Produces a compile error:  Expected: list separator or )
0
 
LVL 3

Expert Comment

by:jcgd
ID: 37834666
to remove al quotes from field, use 'replace' function:

IIf(IsNull(rec!icd_desc), "NULL", """" & TStr(replace(rec!icd_desc,"""","")) & """") &
0
 

Author Closing Comment

by:L_Malchiodi
ID: 37834995
I decided to go with the recordset, and this worked very nicely.  Thank you all for your help!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37835071
:-)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37835186
Did you even try the Quotes function?  That is by far the easiest way to accomplish what you were trying to do.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 37835301
You are welcome!

If I am not updating lots of records, I just find it WAY easier to do it through a recordset.
I've never found a good way to test and debug an UPDATE string, so I am always leery of them
@fyed posted a very useful solution if you are looking to do a large scale update (+1000 records) which will be noticably slower using recordset code that SQL UPDATE

But for small sets, recordset code is nicer to read, nicer to debug and nicer to comment.
Glad you found it useful.

Nick67
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

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…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

749 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