By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,831 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,831 IT Pros & Developers. It's quick & easy.

OpenForm with single quote in criteria

matrekz42
P: 37
Hello there! It's been a long time since I've reached out to the community for assistance, I thought I had this pretty much figured out! But, I've ran into a problem where I cant get the following to work, because in the Invention_Desc, one of the descriptions has a single quote in the string.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Inventions", , , "[Invention_Idea]=" & Me.[Invention_Idea] & " And [Design]='" & Me.[Design] & "'" & " And Invention_Desc = '" & Invention_Desc & "'"
Any recommendations would be awesome! I've been trying to figure this out for hours with no success.

Thank you in advance!
2 Weeks Ago #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,302
It's a good idea always to protect such code by using a function such as SQLSafe().
Expand|Select|Wrap|Line Numbers
  1. 'SQLSafe()  returns a value where all single-quotes (') are doubled up.
  2. '27/03/2019 Updated to handle Variants - especially for Null values.
  3. Public Function SQLSafe(ByVal varIn As Variant) As Variant
  4.     SQLSafe = varIn
  5.     If varIn > "" Then SQLSafe = Replace(CStr(varIn), "'", "''")
  6. End Function
Thus, this particular line of code would be :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Inventions", , , "[Invention_Idea]=" & SQLSafe(Me.[Invention_Idea]) & " And [Design]='" & SQLSafe(Me.[Design]) & "'" & " And Invention_Desc = '" & SQLSafe(Me.Invention_Desc) & "'"
1 Week Ago #2

matrekz42
P: 37
Hi Neo, good to hear from you. I still received the same error. The name has the following: customer's and hence the reason I get the error because of the '.
1 Week Ago #3

NeoPa
Expert Mod 15k+
P: 31,302
Hi Matrekz. You too.

You shouldn't if it's done properly so I wonder exactly how you've implemented the suggestion. Care to share some details?
1 Week Ago #4

twinnyfo
Expert Mod 2.5K+
P: 3,158
This is more of a workaround, rather than a solution, but you can replace the straight quote "'" with a smart quote "".

This prevents the issue, but does not "resolve" it.
1 Week Ago #5

matrekz42
P: 37
Hi Neo, I created a module and entered the function. I then replaced the docmd.openform with
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Inventions", , , "[Invention_Idea]=" & SQLSafe(Me.[Invention_Idea]) & " And [Design]='" & SQLSafe(Me.[Design]) & "'" & " And Invention_Desc = '" & SQLSafe(Me.Invention_Desc) & "'"
1 Week Ago #6

matrekz42
P: 37
thank you twinnyfo, I changed this in the meantime to keep working. I would ultimately like to get the SQLSafe working, but this definitely helped.
1 Week Ago #7

NeoPa
Expert Mod 15k+
P: 31,302
I was sloppy in my earlier post and just made the necessary changes without checking the rest was ok. Try this version :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Inventions", , , "([Invention_Idea]='" & SQLSafe(Me.Invention_Idea) & "') AND ([Design]='" & SQLSafe(Me.Design) & "') AND ([Invention_Desc]='" & SQLSafe(Me.Invention_Desc) & "')"
Or try the way I would do it in my own code :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "([Invention_Idea]='%II') AND " _
  4.        & "([Design]='%D') AND " _
  5.        & "([Invention_Desc]='%ID')"
  6. strSQL = Replace(strSQL, "%II", SQLSafe(Me.Invention_Idea))
  7. strSQL = Replace(strSQL, "%D", SQLSafe(Me.Design))
  8. strSQL = Replace(strSQL, "%ID", SQLSafe(Me.Invention_Desc))
  9. Call DoCmd.OpenForm(FormName:="Inventions", WhereCondition:=strSQL)
That makes it more legible as well as giving you the opportunity to see the resolved string before you run it when debugging.

As a general rule I'm not a great fan of using other types of quotes, either in the code or in the data. It can often get you past bumps in the road but you tend to hit them again later when you're going faster.
1 Week Ago #8

Post your reply

Sign in to post your reply or Sign up for a free account.