Escaping CHR(39) in Query strings

[gemma-the-husky]

I would use chr(34) - dbl quote
This syntax.

It's only an issue if criteria contains a " character.
I expect you could use chr(39) instead of chr(34)

SQL = "SELECT * FROM POINVrec_Line WHERE MaterialID = " & chr(34) & Criteria & chr(34)

 

[cheekybuddha]

I wasn't disagreeing, just 99.9% of the time it's a scenario like the OP's original Criteria = "Mc'Hammer", at least for me.
Should have qualified my statement.

Apologies, I misunderstood! I thought you meant that by using double quotes there would never be a need to escape the input.

However, you know Mr Sod is going to stick his oar in at some point!

 

[MarkK]

QueryDef will work 100% of the time without any modifications to your data and without specifying any delimiters. It is the 'proper' way, it is the simpler way, and it is the most readable way. It solves string delimiter confusion, date delimiter and format confusion like magic. You will, when you go back to your QueryDef code 18 months from now, take no more than 10 seconds to understand exactly what it does, and how it works.

A coded solution is a tool. Choose wisely.

 

[moke123]

QueryDef will work 100% of the time

A few months back I wrote myself a little helper program to generate querydefs and recordsets. I find myself using querydefs more than ever especially when dates are involved. My app imports the table structures of whatever I'm working on. I select a few options , generate the code, and paste it where needed. Saves a lot of typing.

Const Sql_Update As String = _
     "Update tblPeople Set "  &  _ 
          "FirstName = p0 ,"  &  _ 
          "MiddleName = p1 ,"  &  _ 
          "LastName = p2 ,"  &  _ 
          "Suffix = p3 ,"  &  _ 
          "Street = p4 ,"  &  _ 
          "City = p5 ,"  &  _ 
          "State = p6 ,"  &  _ 
          "Zip = p7 "  &  _ 
          "Where PartyID = p8"

     With CurrentDb.CreateQueryDef("", Sql_Update)
          .Parameters(0) = Me.FirstName
          .Parameters(1) = Me.MiddleName
          .Parameters(2) = Me.LastName
          .Parameters(3) = Me.Suffix
          .Parameters(4) = Me.Street
          .Parameters(5) = Me.City
          .Parameters(6) = Me.State
          .Parameters(7) = Me.Zip
          .Parameters(8) = Me.PartyID
     .Execute dbFailOnError
     .Close
     End With

 

[Pat Hartman]

To embed quotes in a string can be confusing so I create a constant and save it in a standard module that holds common stuff or utilities so I can copy it from database to database and always have it handy.

Public Const QUOTE = """"

To use it:

strWhere = " Where sometextfield = " & QUOTE & Me.sometextstring & QUOTE & ";"

This will end up as:

Where sometextfield ="O'Tool"

This solves the problem of having a text field with an embedded apostrophe

You can do the same thing by use Chr() or """" and sometimes """ but the QUOTE is easy to remember and fairly obvious when reading the code.

 

[cheekybuddha]

Still doesn't help if your string contains a double quote.

The simplest solution if building SQL strings in VBA is to have a couple of helper functions:

Public Const ISO_DATETIME     As String = "yyyy\-mm\-dd hh:nn:ss"

Function SQLStr(vIn As Variant, Optional blWrap As Boolean = True) As String

  Dim strRet As String

  If Not IsNull(vIn) Then
    strRet = Replace(vIn, "'", "''")
    If blWrap Then strRet = "'" & strRet & "'"
  Else
    strRet = "NULL"
  End If
  SQLStr = strRet

End Function

Function SQLDate( _
           vIn As Variant, _
           Optional blWrap As Boolean = True, _
           Optional delim As String = "#" _
         ) As String

  Dim strRet As String

  If IsDate(vIn) Then
    strRet = Format(vIn, ISO_DATETIME)
    If blWrap Then strRet = delim & strRet & delim
  ElseIf IsNull(vIn) Then
    strRet = "NULL"
  End If
  SQLDate = strRet

End Function

Then you can build strings and not worry about the contents or the delimiters:

  Dim strSQL As String

  strSQL = "UPDATE YourTable " & _
           "SET NumericField = " & Nz(Me.txtNumber, 0) & ", " & _
               "StringField = " & SQLStr(Me.txtString) & ", "  & _
           "WHERE DateField > " & SQLDate(Me.txtDate) & ";"

Strings can contain both single and double quotes and the SQL will still work.

 

[Pat Hartman]

Still doesn't help if your string contains a double quote.

In that case do it the other way. Make an Apostrophe constant and use that instead of QUOTE. Where you run into trouble is if you are doing something which has embedded single and double quotes as inches and feet symbols. In that case you have to actually "escape" the two marks by doubling them in situ.

Does your code work for:

5' 3"

 

[MarkK]

The simplest solution if building SQL strings in VBA

"escape" the two marks by doubling them in situ.

Or, use a QueryDef....
In this example, if the txtString control contains 5'3", no extra effort is required. It just works. Same with dates. You never have to Format(), delimit, convert to string. The QueryDef handles it, because it knows the types of all the fields already.

Sub Test()
    Const sql As String = _
        "UPDATE YourTable " & _
            "SET NumericField = p0, StringField = p1, " & _
        "WHERE DateField > p2;"
        
    With CurrentDb.CreateQueryDef("", sql)
        .Parameters(0) = Me.txtNumber
        .Parameters(1) = Me.txtString
        .Parameters(2) = Me.txtDate
        .Execute
    End With
End Sub

This code not only replaces all the code in post 26, it also executes the update.

 

[theDBguy]

Or, use a QueryDef....
In this example, if the txtString control contains 5'3", no extra effort is required. It just works. Same with dates. You never have to Format(), delimit, convert to string. The QueryDef handles it, because it knows the types of all the fields already.

Sub Test()
    Const sql As String = _
        "UPDATE YourTable " & _
            "SET NumericField = p0, StringField = p1, " & _
        "WHERE DateField > p2;"
       
    With CurrentDb.CreateQueryDef("", sql)
        .Parameters(0) = Me.txtNumber
        .Parameters(1) = Me.txtString
        .Parameters(2) = Me.txtDate
        .Execute
    End With
End Sub

This code not only replaces all the code in post 26, it also executes the update.

Hi @MarkK. I like your approach. Will this also work for SELECT queries? Just curious...

Sent from phone...

 

[theDBguy]

Hi @MarkK. I like your approach. Will this also work for SELECT queries? Just curious...

Sent from phone...

Oh, never mind. I see you already did that in Post #3. Cheers!

PS. This is what I have always used.

Code Snippets

 

[MarkK]

Yes. To run a SELECT query, check out the QueryDef.OpenRecordset() method. For action queries, use QueryDef.Execute().

 

[theDBguy]

Yes. To run a SELECT query, check out the QueryDef.OpenRecordset() method. For action queries, use QueryDef.Execute().

Yup, saw that. Thanks!

 

[cheekybuddha]

If you don't declare the parameters and their datatypes (as per docs) can/does the QueryDef pick up the datatypes from the underlying table implicitly?

 

[MarkK]

If you don't declare the parameters and their datatypes (as per docs) can/does the QueryDef pick up the datatypes from the underlying table implicitly?

Yes. This is why you no longer need to worry about delimiters. The QueryDef already knows the data type of all the fields.

 

[arnelgp]

its already in the Sample Database:
fnAnySQL() parameter query | Access World Forums (access-programmers.co.uk)

 

[moke123]

I was going to post this the other day when I mentioned it in post#24. I went to clean it up a bit and in doing so, deleted a bunch of code.
When I tried to fix it I decided it would be easier to just re-write it from scratch and did so. It's still a work in progress. It still needs some tweaking but it is what it is, a helper app.

edit: replaced file to fix minor bug.