Encapsulating ' and " in string variables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I encapsulate a string variable to contain ' and " when using the variable in various lookup routines ? For example, the string "a,a'-Dibromo-o-xylene" makes the variable combo1 unreadable by the DLookup()-function below

code = Nz(DLookup("[CHEMICAL]", "CHEMTABLE", "[PRODID]= '" & combo1 & "'"), 0)
 
Basically you want the string to be as follows when it gets to Jet (the db
engine):

[PRODID]="a,a'-Dibromo-o-xylene"

In VBA, to get the double quote to remain in the string such that it is not
treated as a VBA string delimiter you can put a pair of double quotes rather
than the single quote. When VBA processes the string, it will replace the
pair of double quotes (") with a single double quote character. Here is how
your revised code should look:

code = Nz(DLookup("[CHEMICAL]", "CHEMTABLE", "[PRODID]= """ & combo1 &
""""), 0)

Same line, split over two lines with line continuation for clarity:

code = Nz(DLookup("[CHEMICAL]", "CHEMTABLE", _
"[PRODID]= """ & combo1 & """"), 0)
 
Back
Top