Solved

Handling double quotes in a string

Posted on 2012-04-11
12
505 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dynamically Reorder List Box 4 39
Turn off MS Access Default=0 for Numerics 6 27
Report with several filters - Issue with query? 3 23
Switch 5 17
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…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
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…

803 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