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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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) & ") & ", "
Dale FyeOwner, Developing Solutions LLCCommented:
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
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.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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;
Dale FyeOwner, Developing Solutions LLCCommented:
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 & ";"
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)) & ",
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 )
jcgdCommented:
to remove al quotes from field, use 'replace' function:

IIf(IsNull(rec!icd_desc), "NULL", """" & TStr(replace(rec!icd_desc,"""","")) & """") &
L_MalchiodiAuthor Commented:
I decided to go with the recordset, and this worked very nicely.  Thank you all for your help!
Rey Obrero (Capricorn1)Commented:
:-)
Dale FyeOwner, Developing Solutions LLCCommented:
Did you even try the Quotes function?  That is by far the easiest way to accomplish what you were trying to do.
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
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.