[AccessD] Handling strings in VBA SQL scripts?

Hi Harry

> I am searching for a function that determines the need for barcketing and
> need for apostrophing/stringing of variable names. For example a tablename
> stored in variable strTableName with value "table1" would not need
> bracketing, while value "table 1" definitly needs bracketing, e.g. as part
> of an SQL string "FROM [" & strTableName & "]".

Brackets don't harm, so just apply them if you feel they could be
needed.

> To make it a bit more difficult the value could also be "John's Table". This
> would cause problems when we need to string the variable in an SQL string,
> e.g. "SELECT '" & strTableName & "' AS TableName, " would result in "SELECT
> 'Peter's table' AS FromTable," which obviously is not correct, it should be
> "SELECT 'Peter''s table' AS FromTable,".  It would also cause problems when
> we decide to code using something like rst(strTableName).

Jürgen Welz have posted a proven clean-up function for this purpose:

A problem with the """" or chr$(34) and such solutions is that if you have a
string with apostrophes in it, like "John O'Conner" or "8' 4" Block Wall", 
the sql/find/dAgg will choke.  My users are accustomed to mixing both quotes 
and apostrophes in their input fields (although we are metric here, we still 
get a fair bit of feet and inches stuff and our new Seattle office is 
definitely going to be using imperial units of measure).  The 97 ADH has a 
general solution for fixing mixed quotes and apostrophes.... but it doesn't 
work.  I use:

<quote>

Function fnFixQuotes(Srch As String) As String
    Dim strout As String
    Dim Qt As String
    Dim IntI As Integer

    Qt = """"
    For IntI = 1 To Len(Srch)
        If InStr(Chr$(34), Mid$(Srch, IntI, 1)) Then
            strout = strout & Chr$(34) & "& Chr$(34) &" & Chr$(34)
        Else
            strout = strout & Mid$(Srch, IntI, 1)
        End If
    Next
    fnFixQuotes = Qt & strout & Qt
End Function

An example of how it's called with wildcards to find a string anywhere in a 
field:

Private Sub txtNameCrit_AfterUpdate()
    Dim strCrit As String
    If Not IsNull(Me.txtNameCrit) Then
        strCrit = Me.txtNameCrit
        strCrit = fnFixQuotes("*" & strCrit & "*")
    Else
        strCrit = ""
    End If
    Call GetCompanies(strCrit)
End Sub

Private Sub GetCompanies(strIn)
    Dim strSql As String
    With Me
       If len(strCrit) Then
        strSql = "SELECT CompanyID, CompanyName FROM tblCompany WHERE 
CompanyID > 0 AND " & _
                            "Deleted = False AND CompanyName Like " & strIn 
& " ORDER BY CompanyName WITH OWNERACCESS OPTION"
        .lstAvailable.RowSource = strSql
        .lstAvailable.SetFocus
       Else
          'sql without criteria
       End if
    End With
End Sub

If you use a function like this to generate your delimiters, it is 
completely unnecessary to use special delimiters in your sql/find/dAgg code. 
  I've never written a fix to the pipe character (which chokes any sql) but 
that can be mostly fixed by replacing it a token).  You can write a more 
generic procedure yet that will choose your date or string delimiters and 
apply them where necessary.  I always thought that if you declared a 
variable as a string, Access should implicitly add the delimiters.  It would 
have been easy enough for them to build in an internal delimiter function to 
look after this ubiquitous issue.

Ciao
Jürgen Welz
Edmonton, Alberta

</quote>

/gustav