14.4. The ReplaceOrderByClause and ReplaceWhereClause Functions

It is often necessary to "cut and replace" the Where and Order By clauses of a SQL string using VBA. Throughout this chapter, we used the ReplaceWhereClause and ReplaceOrderByClause functions to do this. Finally, here is the code that was doing all that hard work!

This first procedure ParseSQL does the "heavy lifting" of the SQL handling functions. It breaks up the original SQL string into components, so that individual pieces can be replaced. Although ParseSQL is Public, it will rarely be called from anywhere other than the ReplaceWhereClause and ReplaceOrderByClause functions that follow it.

Option Compare Database
Option Explicit

Public Sub ParseSQL(strSQL As Variant, strSELECT As Variant, strWhere As
Variant, strOrderBy As Variant, strGROUPBY As Variant, strHAVING As Variant)

On Error GoTo Error_Handler
'
'This subroutine accepts a valid SQL string and passes back separated
SELECT, WHERE, ORDER BY and GROUP BY clauses.
'
'INPUT:
'    strSQL    valid SQL string to parse
'OUTPUT:
'     strSELECT SELECT portion of SQL (includes JOIN info)
'     strWHERE WHERE portion of SQL
'     strORDERBY ORDER BY portion of SQL
'     strGROUPBY GROUP BY portion of SQL
'     strHAVING HAVING portion of SQL
'
'Note: While the subroutine will accept the ';' character in strSQL,
'      there is no ';' character passed back at any time.

Note that this Sub takes in only one parameter (the original SQL string), but modifies and outputs five parameters; one for each portion of the parsed SQL string.

Dim intStartSELECT As Integer
Dim intStartWHERE As Integer
Dim intStartORDERBY As Integer
Dim intStartGROUPBY As Integer
Dim intStartHAVING As Integer

Dim intLenSELECT As Integer
Dim intLenWHERE As Integer
Dim intLenORDERBY As Integer
Dim intLenGROUPBY As Integer
Dim intLenHAVING As Integer

Dim intLenSQL As Integer

This next code determines the starting location of each clause in the SQL statement by finding the position in the string of the corresponding keywords.

intStartSELECT = InStr(strSQL, "SELECT ")
intStartWHERE = InStr(strSQL, "WHERE ")
intStartORDERBY = InStr(strSQL, "ORDER BY ")
intStartGROUPBY = InStr(strSQL, "GROUP BY ")
intStartHAVING = InStr(strSQL, "HAVING ")

'if there's no GROUP BY, there can't be a HAVING
If intStartGROUPBY = 0 Then
    intStartHAVING = 0
End If

If InStr(strSQL, ";") Then 'if it exists, trim off the ';'
    strSQL = Left(strSQL, InStr(strSQL, ";") - 1)
End If

intLenSQL = Len(strSQL)

The following section of code calculates the length of the Select clause of the SQL statement. Basically, it starts by assuming that the Select clause is the entire remaining length of the SQL statement and then tries shorter and shorter lengths by testing against the starting positions of the other SQL clauses.

' find length of Select portion
If intStartSELECT > 0 Then
    ' start with longest it could be
    intLenSELECT = intLenSQL - intStartSELECT + 1
    If intStartWHERE > 0 And intStartWHERE > intStartSELECT _
    And intStartWHERE < intStartSELECT + intLenSELECT Then
        'we found a new portion closer to this one
        intLenSELECT = intStartWHERE - intStartSELECT
    End If
    If intStartORDERBY > 0 And intStartORDERBY > intStartSELECT _
    And intStartORDERBY < intStartSELECT + intLenSELECT Then
        'we found a new portion closer to this one
        intLenSELECT = intStartORDERBY - intStartSELECT
    End If
    If intStartGROUPBY > 0 And intStartGROUPBY > intStartSELECT _
    And intStartGROUPBY < intStartSELECT + intLenSELECT Then
        'we found a new portion closer to this one
        intLenSELECT = intStartGROUPBY - intStartSELECT
    End If
    If intStartHAVING > 0 And intStartHAVING > intStartSELECT _
    And intStartHAVING < intStartSELECT + intLenSELECT Then
        'we found a new portion closer to this one
        intLenSELECT = intStartHAVING - intStartSELECT
    End If
End If

This next section of code does the same thing for the Group By clause. It determines the length of the Group By clause finding the beginning of the next clause.

' find length of GROUPBY portion
If intStartGROUPBY > 0 Then
    ' start with longest it could be
    intLenGROUPBY = intLenSQL - intStartGROUPBY + 1
    If intStartWHERE > 0 And intStartWHERE > intStartGROUPBY _
    And intStartWHERE < intStartGROUPBY + intLenGROUPBY Then
        'we found a new portion closer to this one
        intLenGROUPBY = intStartWHERE - intStartGROUPBY
    End If
    If intStartORDERBY > 0 And intStartORDERBY > intStartGROUPBY _
    And intStartORDERBY < intStartGROUPBY + intLenGROUPBY Then
        'we found a new portion closer to this one
        intLenGROUPBY = intStartORDERBY - intStartGROUPBY
    End If
    If intStartHAVING > 0 And intStartHAVING > intStartGROUPBY _
    And intStartHAVING < intStartGROUPBY + intLenGROUPBY Then
        'we found a new portion closer to this one
        intLenGROUPBY = intStartHAVING - intStartGROUPBY
    End If
End If

The following one does the same thing for the Having clause:

' find length of HAVING portion
If intStartHAVING > 0 Then

    ' start with longest it could be
    intLenHAVING = intLenSQL - intStartHAVING + 1
    If intStartWHERE > 0 And intStartWHERE > intStartHAVING _
    And intStartWHERE < intStartHAVING + intLenHAVING Then
        'we found a new portion closer to this one
        intLenHAVING = intStartWHERE - intStartHAVING
    End If
    If intStartORDERBY > 0 And intStartORDERBY > intStartHAVING _
    And intStartORDERBY < intStartHAVING + intLenHAVING Then
        'we found a new portion closer to this one
        intLenHAVING = intStartORDERBY - intStartHAVING
    End If
    If intStartGROUPBY > 0 And intStartGROUPBY > intStartHAVING _
    And intStartGROUPBY < intStartHAVING + intLenHAVING Then
        'we found a new portion closer to this one
        intLenHAVING = intStartGROUPBY - intStartHAVING
    End If
End If

This does the same thing for the Order By clause:

' find length of ORDERBY portion
If intStartORDERBY > 0 Then
    ' start with longest it could be
    intLenORDERBY = intLenSQL - intStartORDERBY + 1
    If intStartWHERE > 0 And intStartWHERE > intStartORDERBY _
    And intStartWHERE < intStartORDERBY + intLenORDERBY Then
        'we found a new portion closer to this one
        intLenORDERBY = intStartWHERE - intStartORDERBY
    End If
    If intStartGROUPBY > 0 And intStartGROUPBY > intStartORDERBY _
    And intStartGROUPBY < intStartORDERBY + intLenORDERBY Then
        'we found a new portion closer to this one
        intLenORDERBY = intStartGROUPBY - intStartORDERBY
    End If
    If intStartHAVING > 0 And intStartHAVING > intStartORDERBY _
    And intStartHAVING < intStartORDERBY + intLenORDERBY Then
        'we found a new portion closer to this one
        intLenORDERBY = intStartHAVING - intStartORDERBY
    End If
End If

Finally, the length of the Where clause is determined.

' find length of WHERE portion
If intStartWHERE > 0 Then
    ' start with longest it could be
    intLenWHERE = intLenSQL - intStartWHERE + 1

If intStartGROUPBY > 0 And intStartGROUPBY > intStartWHERE _
And intStartGROUPBY < intStartWHERE + intLenWHERE Then

        'we found a new portion closer to this one
        intLenWHERE = intStartGROUPBY - intStartWHERE
    End If
    If intStartORDERBY > 0 And intStartORDERBY > intStartWHERE _
    And intStartORDERBY < intStartWHERE + intLenWHERE Then
        'we found a new portion closer to this one
        intLenWHERE = intStartORDERBY - intStartWHERE
    End If
    If intStartHAVING > 0 And intStartHAVING > intStartWHERE _
    And intStartHAVING < intStartWHERE + intLenWHERE Then
        'we found a new portion closer to this one
        intLenWHERE = intStartHAVING - intStartWHERE
    End If
End If

Now that all the starting positions and lengths of the five SQL clauses have been determined, the output parameters can be set:

' set each output portion
    If intStartSELECT > 0 Then
        strSELECT = Mid$(strSQL, intStartSELECT, intLenSELECT)
    End If
    If intStartGROUPBY > 0 Then
        strGROUPBY = Mid$(strSQL, intStartGROUPBY, intLenGROUPBY)
    End If
    If intStartHAVING > 0 Then
        strHAVING = Mid$(strSQL, intStartHAVING, intLenHAVING)
    End If
    If intStartORDERBY > 0 Then
        strOrderBy = Mid$(strSQL, intStartORDERBY, intLenORDERBY)
    End If
    If intStartWHERE > 0 Then
        strWhere = Mid$(strSQL, intStartWHERE, intLenWHERE)
    End If

Exit_Procedure:
    Exit Sub

Error_Handler:
    MsgBox Error.Number & ": " & Error.Description
    Resume Exit_Procedure
End Sub

The next two functions merely use the ParseSQL procedure to break up the SQL statement into its five clauses, and then they replace the appropriate clause with the new clause that was passed in.

Public Function ReplaceWhereClause(strSQL As Variant, strNewWHERE As
Variant)
On Error GoTo Error_Handler

'This subroutine accepts a valid SQL string and Where clause, and 'returns the same SQL statement with the original Where clause (if any)
'replaced by the passed in Where clause.
'
'INPUT:
'     strSQL    valid SQL string to change
'OUTPUT:
'     strNewWHERE New WHERE clause to insert into SQL statement
'
Dim strSELECT As String, strWhere As String
Dim strOrderBy As String, strGROUPBY As String, strHAVING As String

Call ParseSQL(strSQL, strSELECT, strWhere, strOrderBy, _
strGROUPBY, strHAVING)

ReplaceWhereClause = strSELECT & " " & strNewWHERE & " " _
& strGROUPBY & " " & strHAVING & " " & strOrderBy

Exit_Procedure:
     Exit Function
Error_Handler:
     MsgBox Err.Number & ", " & Err.Description
     Resume Exit_Procedure
End Function

Public Function ReplaceOrderByClause(strSQL As Variant, strNewOrderBy As
Variant)
On Error GoTo Error_Handler
'
'This subroutine accepts a valid SQL string and Where clause, and
'returns the same SQL statement with the original Where clause (if any)
'replaced by the passed in Where clause.
'
'INPUT:
'   strSQL valid SQL string to change
'OUTPUT:
'    strNewOrderBy New OrderBy clause to insert into SQL statement
'
Dim strSELECT As String, strWhere As String
Dim strOrderBy As String, strGROUPBY As String, strHAVING As String

Call ParseSQL(strSQL, strSELECT, strWhere, strOrderBy, _
strGROUPBY, strHAVING)

ReplaceOrderByClause = strSELECT & " " & strWhere & " " & strNewOrderBy
     Exit_Procedure:
Exit Function
     Error_Handler:
     MsgBox Err.Number & ", " & Err.Description
     Resume Exit_Procedure
End Function

These SQL handling procedures can be added to all of your Access applications in their own module, such as basSQLTools. By using ReplaceWhereClause and ReplaceOrderByClause you can take a lot of the hassle out of manipulating SQL strings in your VBA code.