UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Handling Apostrophes In SQL Strings In VBA, Any Version    
 
   
BruceM
post Feb 19 2019, 08:56 AM
Post#1


UtterAccess VIP
Posts: 7,993
Joined: 24-May 10
From: Downeast Maine


When assembling a SQL string in VBA, Access will sometimes interpret an apostrophe as the end of the string, but then there is more text after the end of the string, and Access doesn't know what to do. For instance, if the business is Mel's Diner in Phoenix, you may have this:

Dim strSQL As String

strSQL = "SELECT [Comments] FROM tblBusiness WHERE BusinessName LIKE '*" & Me.txtSearch & "*'"

If txtSearch contains the value "Diner", strSQL would be:

SELECT [Comments] FROM tblBusiness WHERE BusinessName LIKE '*Diner*'

This should be OK. However, if the search value is "Mel's" you would end up with:

SELECT [Comments] FROM tblBusiness WHERE BusinessName LIKE '*Mel's'*'

Access doesn't know what to do with that.

One approach is to replace each single quote with a double quote, so you would have:

strSQL = "SELECT [Comments] FROM tblBusiness WHERE BusinessName LIKE ""*" & Me.txtSearch & "*"""

I prefer a simple wrapper function I got from Stefan Hoffman in a newgroup posting in 2007. It resolves both single and double quotes within a string. With full acknowledgement and gratitude to Stefan, here it is:

CODE
'---------------------------------------------------------------------------------------
' Procedure : SQLQuote
' Author    : Stefan Hoffman, from a newsgroup posting 20-Sep-2007
' Purpose   : Resolve quotes within string arguments:
'             strWhere = "LastName = " & SQLQuote(Me.txtLastName)
'---------------------------------------------------------------------------------------
'
Public Function SQLQuote _
    (ByRef AString As String) _
   As String

  Const Delimiter As String = "'"

   SQLQuote = _
    Delimiter & _
    Replace(AString, Delimiter, Delimiter & Delimiter) & _
    Delimiter

End Function

In the example above, it would be used like this:

strSQL = "SELECT [Comments] FROM tblBusiness WHERE BusinessName LIKE " & SQLQuote("*" & Me.txtSearch & "*")
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2019 - 04:20 AM