Access 2003 VBA Programmer's Reference

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 ...