Solved How to search with an apostrophe without throwing up run-time error '3075'

[MediaHolic]

Hello again,

Struggled with this until admitting defeat, back to my steelbook database and the film title "Marvel's Avengers", when I run the VBA filter.

From the main form, a sub form called frm_Allitems5.

Private Sub Command199_Click()

DoCmd.OpenForm "frm_AllItems5", , , "[Film Title] = " & "'" & [FilmTitle] & "'"

Works with all other film names but as Marvel's Avengers contains an apostrophe it throws up the error. Without removing the apostrophe (lazy solution) is there a way or syntax to include the apostrophe in the search?

Thanks in advance.

 

[Josef P.]

DoCmd.OpenForm "frm_AllItems5", , , "[Film Title] = " & "'" & Replace([FilmTitle], "'", "''") & "'"

=> Important against sql injection

To make this easier to read, I recommend creating an extra function for this.

DoCmd.OpenForm "frm_AllItems5", , , "[Film Title] = " & TextToSqlText([FilmTitle])

Public Function TextToSqlText(ByVal Value As Variant, _
                     Optional ByVal Delimiter As String = "'", _
                     Optional ByVal WithoutLeftRightDelim As Boolean = False) As String
  
   Dim Result As String
  
   If IsNull(Value) Then
      TextToSqlText = "Null"
      Exit Function
   End If
  
   Result = Replace$(Value, Delimiter, Delimiter & Delimiter)
   If Not WithoutLeftRightDelim Then
      Result = Delimiter & Result & Delimiter
   End If
  
   TextToSqlText = Result

End Function

Code from: https://github.com/AccessCodeLib/AccessCodeLib/blob/master/data/SqlTools.cls#L456-L474

 

[MediaHolic]

@Josef P.

​

Thank you for input, I was looking for an easier solution as I'm still cutting my teeth with VBA. That function is a little beyond my experience at present as I'd like to know both the syntax and how/why it works which I'll build up to, the help is appreciated.

After experimentation, this works perfectly:

DoCmd.OpenForm "frm_AllItems5", , , "[Film Title] = " & Chr(34) & [FilmTitle] & Chr(34)

As does your code of:

DoCmd.OpenForm "frm_AllItems5", , , "[Film Title] = " & "'" & Replace([FilmTitle], "'", "''") & "'"

Which I think I'll use instead of mine!

 

[arnelgp]

i would go with Josef P. solution.

you will still get error on your 1st solution if there is a double quote (") on your [Film Title] field.

 

[Josef P.]

.. and also try this filter value: abc" or "a" ="a

 

[MediaHolic]

@arnelgp

This is true, absolutely agree. The only shifted character out of all the titles is the apostrophe in Marvel's.

I'll try and get to grips with the code and apply it at a later date. I'm still learning and I'd like to be able to create the code and know how and why it works, I'm just not at that level of experience yet.

 

[Josef P.]

short description as code:

Dim FilmTitleToFilter as String
Dim CriteriaString as String
FilmTitleToFilter = "Marvel's Avengers"

' 1. wrong code:
CriteriaString = "[Film Title] = '" & FilmTitleToFilter & "'"
Debug.Print CriteriaString
'=> [Film Title] = 'Marvel's Avengers' => ' is the string marker in SQL => 1. String = Marvel => syntax error for the rest

' 2. correct sql: double the ' inside the string => replace ' inside the text with '' (2x ')
CriteriaString = "[Film Title] = '" & Replace(FilmTitleToFilter, "'", "''") & "'"
Debug.Print CriteriaString
'=> [Film Title] = 'Marvel''s Avengers' => string for database engine (after sql interpreter) is Marvel's Avengers

 

[MediaHolic]

short description as code:

Dim FilmTitleToFilter as String
Dim CriteriaString as String
FilmTitleToFilter = "Marvel's Avengers"

' 1. wrong code:
CriteriaString = "[Film Title] = '" & FilmTitleToFilter & "'"
Debug.Print CriteriaString
'=> [Film Title] = 'Marvel's Avengers' => ' is the string marker in SQL => 1. String = Marvel => syntax error for the rest

' 2. correct sql: double the ' inside the string => replace ' inside the text with '' (2x ')
CriteriaString = "[Film Title] = '" & Replace(FilmTitleToFilter, "'", "''") & "'"
Debug.Print CriteriaString
'=> [Film Title] = 'Marvel''s Avengers' => string for database engine (after sql interpreter) is Marvel's Avengers

Thank you for taking the time to explain this, very much appreciated.

 

[Gasman]

@Josef P.

​

Thank you for input, I was looking for an easier solution as I'm still cutting my teeth with VBA. That function is a little beyond my experience at present as I'd like to know both the syntax and how/why it works which I'll build up to, the help is appreciated.

After experimentation, this works perfectly:

DoCmd.OpenForm "frm_AllItems5", , , "[Film Title] = " & Chr(34) & [FilmTitle] & Chr(34)

As does your code of:

DoCmd.OpenForm "frm_AllItems5", , , "[Film Title] = " & "'" & Replace([FilmTitle], "'", "''") & "'"

Which I think I'll use instead of mine!

It is not hard, you just pass a parameter and it does it all for you.

 

[MediaHolic]

Thank you, as your signature says "Teach a man to fish and you feed him for life"

 

[MajP]

Here is another function that handles apostrophes, date delimiters, null values, booleans, numerics

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

Same as @Josef P. example. You do not need to understand how the watch works you just need to know how to use it.

DoCmd.OpenForm "frm_AllItems5", , , "[Film Title] = " & Csql([FilmTitle])

 

[MediaHolic]

Thank you, this has given me a lot to work through and learn. The way my head works, I need to understand how and why it works.