escaping the apostrophe

[Carmine]

Private Sub Form_Open(Cancel As Integer)
Me.Filter = ""
Me.FilterOn = False
Me!CountryCbo = Null
Me!VarietyCbo = Null
End Sub

Private Sub VarietyCbo_AfterUpdate()
RequeryForm
End Sub

Private Sub CountryCbo_AfterUpdate()
RequeryForm
End Sub

Sub RequeryForm()
'Changes the forms filter
Dim strSQL As String 'filter clause

strSQL = ""
If Len("" & Me!CountryCbo) > 0 Then
strSQL = "[Cntry]='" & Me!CountryCbo & "'"
End If

If Len("" & Me!VarietyCbo) > 0 Then


If Len(strSQL) > 0 Then
strSQL = strSQL & "And"
End If
strSQL = strSQL & "[Vriety]='" & Me!VarietyCbo & " ' "
End If

If Len(strSQL) = 0 Then

Me.FilterOn = False 'if empty then turn filter off
Else

Me.Filter = strSQL 'otherwise turn it on <<<<Statement to where error 3075 points
Me.FilterOn = True
End If

End Sub
-------------------------------------------------------
The above code works perfectly except, when there is an apostrophe (‘) in Vriety then the dreaded error 3075. I know that I must use an escape procedure, but what code and where must the code be placed?
Can the code be placed in a module and then called as needed? If so how is this done?

[June7]

Please post code between CODE tags to retain indentation and readability, especially longer snippets.

This is a common topic.

Options for constructing Filter string:

1) strSQL = strSQL & "[Vriety]=""" & Me!VarietyCbo & """"

2) strSQL = strSQL & "[Vriety]='" & Replace(Me!VarietyCbo, "'", "''") & "'"

Yes, can have a custom function that receives string as argument and returns string modified with doubled apostrophes if needed. But why bother? Already have intrinsic function to do that - Replace().

[Ken Sheridan]

Bearing in mind that my name in is original non-anglicized form is Cináed O'Siridean, I would always use a continuous pair of double quote characters "" to represent a literal quote character. If a string might include double quote characters, then I would call the Replace function to return them with a high ASCII character such as the tilde ~ in both the string expression and the criteria.

[moke123]

You can also use a helper function like below.
I happen to use an enum because it is used in several procedures.

you'd call it like

strSQL = strSQL & "[Vriety]=" & Dlmt(Me!VarietyCbo,DoubleQuotes)

Public Enum eDelimiterType   
    NoDelimiter = 0
    DoubleQuotes = 1
    Octothorpes = 2
    SingleQuotes = 3
End Enum

Public Function Dlmt(objIN As Variant, Optional Delimiter As eDelimiterType = 1) As Variant
'returns the passed in value wrapped with the selected delimiter


    On Error GoTo Dlmt_Error
    
    Dim DeLimit As String


    Select Case Delimiter
        Case 0
            DeLimit = Null
        Case 1
            DeLimit = Chr(34) 'Quotes
        Case 2
            DeLimit = Chr(35) 'Octothorpes
        Case 3
            DeLimit = Chr(39) 'SingleQuotes
    End Select
                
    Dlmt = DeLimit & objIN & DeLimit
    
    On Error GoTo 0
    Exit Function


Dlmt_Error:


    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Dlmt, line " & Erl & "."


End Function

[Gustav]

You can implement a function that does this automatically:

' Converts a value of any type to its string representation.
' The function can be concatenated into an SQL expression as is
' without any delimiters or leading/trailing white-space.
'
' Examples:
'   SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
'   SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
'
'   SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
'   SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
'
' Trims text variables for leading/trailing Space and secures single quotes.
' Replaces zero length strings with Null.
' Formats date/time variables as safe string expressions.
' Uses Str to format decimal values to string expressions.
' Returns Null for values that cannot be expressed with a string expression.
'
' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function CSql( _
    ByVal Value As Variant) _
    As String

    Const vbLongLong    As Integer = 20
    Const SqlNull       As String = " Null"

    Dim Sql             As String
    Dim LongLong        As Integer

    #If Win32 Then
        LongLong = vbLongLong
    #End If
    #If Win64 Then
        LongLong = VBA.vbLongLong
    #End If

    Select Case VarType(Value)
        Case vbEmpty            '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          '    2  Integer.
            Sql = Str(Value)
        Case vbLong             '    3  Long integer.
            Sql = Str(Value)
        Case vbSingle           '    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble           '    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency         '    6  Currency.
            Sql = Str(Value)
        Case vbDate             '    7  Date.
            Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
        Case vbString           '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " '" & Sql & "'"
            End If
        Case vbObject           '    9  Object.
            Sql = SqlNull
        Case vbError            '   10  Error.
            Sql = SqlNull
        Case vbBoolean          '   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant          '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          '   14  Decimal.
            Sql = Str(Value)
        Case vbByte             '   17  Byte.
            Sql = Str(Value)
        Case LongLong           '   20  LongLong integer (Valid on 64-bit platforms only).
            Sql = Str(Value)
        Case vbUserDefinedType  '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            ' 8192  Array.
            Sql = SqlNull
        Case Else               '       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Sql & " "

End Function