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.

 

[Isaac]

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.

I personally think this is a VERY good idea and you ARE on the right path.

My rule for myself, to both keep myself honest and protect my employers, is I don't use any code I don't understand.
I mean really understand it. Several times I have declined to accept what appear to be nifty solutions from the internet in favor of my own code because I didn't quite understand them (yet) and needed to quickly move on to meet a deadline - I preferred inferior code I understand to potentially superior code that becomes a black box to me.

If you start just copying/pasting code from the internet and it ends up all over your database, that is a great way to create tons of risk and generate problems that you then will not be able to solve, as you didn't understand the starting code to begin with. And is just flat-out irresponsible programming.

Just my opinion - you're on the right path to make sure you understand anything that becomes part of your application.

 

[Gasman]

That said, people here post 'example code and the O/P copies and pastes it without any clue, so the names do not match, etc.

 

[Isaac]

That said, people here post 'example code and the O/P copies and pastes it without any clue, so the names do not match, etc.

Exactly

 

[MediaHolic]

I personally think this is a VERY good idea and you ARE on the right path.

My rule for myself, to both keep myself honest and protect my employers, is I don't use any code I don't understand.
I mean really understand it. Several times I have declined to accept what appear to be nifty solutions from the internet in favor of my own code because I didn't quite understand them (yet) and needed to quickly move on to meet a deadline - I preferred inferior code I understand to potentially superior code that becomes a black box to me.

If you start just copying/pasting code from the internet and it ends up all over your database, that is a great way to create tons of risk and generate problems that you then will not be able to solve, as you didn't understand the starting code to begin with. And is just flat-out irresponsible programming.

Just my opinion - you're on the right path to make sure you understand anything that becomes part of your application.

Thank you, the kind words and support are very much appreciated. When I get to a decent standard of knowledge, hopefully I can also contribute back and help others who are in the position that I am now as people have on here.

I have a need to know, if I make ten mistakes to find the answer I'd rather do it that way, then I found out ten ways how not to code the solution.

Thank you for your understanding.