Separating sql parts

J

John

Hi

Bit of a peculiar question. How can I separate the SELECT, FROM , WHERE and
ORDER BY parts of a sql strings into separate strings? The reason is I need
to replace WHERE and ORDER BY sections of SQL for different situations and
it is handy to dissect the sql and then reassemble as needed.

Thanks

Regards
 
A

Allen Browne

John, I'm not sure there is a simple way to do this.

You can use Instr() to locate a string such as "SELECT" within a SQL
statement (such as the SQL property of a QueryDef), but there are several
pitfalls, e.g.:
- Access permits field named as keywords (sometimes in square brackets.)

- You will need to use Regular Expressions (to handle brackets,
line-endings, not just spaces.)

- Queries can contain subqueries. (So recursive code?)

You will probably find that the simplest approach is to store the clauses
separately and assemble them in code, rather than try to disassemble a
complete SQL statement and put it back together. Example:
Dim strWhere As String
Const strcStub = "SELECT Table1.* FROM Table1 "
strWhere = "WHERE Field1 = 99 "
Debug.Print strcStub & strWhere
 
Top