Solved

Handling double quotes in a string

Posted on 2012-04-11
12
503 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
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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)
Comment Utility
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
Comment Utility
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
 

Author Comment

by:L_Malchiodi
Comment Utility
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)
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:L_Malchiodi
Comment Utility
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
Comment Utility
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
Comment Utility
I decided to go with the recordset, and this worked very nicely.  Thank you all for your help!
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
:-)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 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