0

I am doing Data base coding based on a large quantity of driving material descriptions. A ton (Thousands) of these variables include measurements such as 1' x 4" Steel Rod for example

I can't escape the quote in the criteria SQL because my criteria is dynamic. How else can I escape the quote?

If IsNull(Me.part.Value) = True Then
'if no part entered, use description
Description = Me.desc.Value
'look up the data on this part
Me.Quantity.Value = Nz(DLookup("[Inventory]", "myQuery", "[myQuery]!_
[Material Description] ='" & Description & "' And [myQuery]![Dept] ='" & dept & "'"))

where description is from a combobox that is filled based on department such as 1' x 4" Steel Rod

EDIT: Most questions that this would be related as duplicate are looking for single quotes only such as names with single quotes whereas I have fields with several single and double quotes int he same criteria

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
scott
  • 153
  • 11
  • Here are 2 solutions: 1) Use parameterized query. See here: http://stackoverflow.com/questions/31059553/how-to-run-parameterized-query-from-vba-parameters-sourced-from-recordset 2) create `Parameterize` procedure that will scrub your string by replacing single and double quotes by a double version of itself. – OpiesDad Dec 21 '16 at 20:35
  • 1
    try `REPLACE(Description,"'","''")` – D Stanley Dec 21 '16 at 20:36
  • 1
    See here: http://stackoverflow.com/questions/13134070/dealing-with-single-quotes-in-excel-vba-sql-queries for an example of the second solution (without a procedure, just in the SQL build) – OpiesDad Dec 21 '16 at 20:37
  • Alternatively, use my _CSql_ function listed [here](http://stackoverflow.com/questions/36996045/insert-order-to-ms-access-from-vb6/36997461#36997461) to concatenate the values in a structured manner. – Gustav Dec 22 '16 at 07:07
  • Thanks for the responses guys, the replace funtion seems to eb what i am looking for. I dont ge tthe syntax error in my dlookup now, but the parameter still isnt finding the term in my domain with the new string:test1 = Me.desc.Value `test2 = Replace(test1, "'", "''") Dim DQ As String DQ = Chr(34) test1 = Replace(test2, DQ, DQ & DQ) ` @DStanley – scott Dec 22 '16 at 15:02
  • my dlookup criteria went from 1' x 4" Steel Rod to 1'' x 4"" Steel Rod but returned no results from my domain inventory where there is definitely an entry 1' x 4" Steel Rod. do i have to replace the quotes in the domain too somehow? @DStanley – scott Dec 22 '16 at 15:06
  • You shouldn't need to replace double-quotes within a string variable. You'd only need to do that in a string _literal_. – D Stanley Dec 22 '16 at 15:07
  • @DStanley Okay, removed the double quotes code, runs through with no syntax error. Okay terrific. I should've just taken your exact advice instead of extrapolating. Thank you very much – scott Dec 22 '16 at 15:13

0 Answers0