Handling double quotes in a string

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!!
L_MalchiodiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
use chr(34) as wrapper

"icd_desc = " & IIf(IsNull(rec!icd_desc), "NULL", " & chr(34) &  TStr(rec!icd_desc) & chr(34) & ") & ", "
0
Dale FyeCommented:
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
Nick67Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

L_MalchiodiAuthor Commented:
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
Dale FyeCommented:
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
Rey Obrero (Capricorn1)Commented:
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
L_MalchiodiAuthor Commented:
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
jcgdCommented:
to remove al quotes from field, use 'replace' function:

IIf(IsNull(rec!icd_desc), "NULL", """" & TStr(replace(rec!icd_desc,"""","")) & """") &
0
L_MalchiodiAuthor Commented:
I decided to go with the recordset, and this worked very nicely.  Thank you all for your help!
0
Rey Obrero (Capricorn1)Commented:
:-)
0
Dale FyeCommented:
Did you even try the Quotes function?  That is by far the easiest way to accomplish what you were trying to do.
0
Nick67Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.