P.S. - I've gone ahead with my project, and gratefully
used your snippet, but I did want to point out that what I
was looking for wasn't a simple search for "ORDER BY",
but, rather, a routine that would return the SELECT clause
and the WHERE clause and the ORDER BY clause, etc., etc.,
etc. so that you could manipulate the whole thing for a
variety of purposes. But, 24 hours is long enough to
wait. Thanks again.
-----Original Message-----
Hi,
Sometimes it takes less time to write your own than to
wait days to find a pre-written routine, plus
you always learn something when you write your own.
Place this in a standard module:
Public Sub ParseSql(strSqlIn As String, mainSqlOut As String, orderByOut As String)
If InStr(1, strSqlIn, "Order By", vbTextCompare) <> 0 Then
mainSqlout = Trim(Left(strSqlIn, InStr(1,
strSqlIn, "Order By", vbTextCompare) - 1))
orderByOut = Trim(Mid(strSqlIn, InStr(1,
strSqlIn, "Order By", vbTextCompare)))
Else
mainSqlOut = strSqlIn
orderByOut = ""
End If
End Sub
Call it like this:
Dim strSql As String
Dim strOrderBy As String
'ParseSql "Select * From table1 Order By something", strSql, strOrderBy
'ParseSql "Select * From table1", strSql, strOrderBy
MsgBox strSql
MsgBox strOrderBy
--
HTH
Dan Artuso, Access MVP
"Laurel" <
[email protected]> wrote in
message news:
[email protected]...
Maybe I wasn't clear. I need to take pre-existing SQL
(the base SQL Select for various charts), and insert the
WHERE clause. Some of this pre-existing SQL has ORDER BY
clauses, for an example, so I can't just tack the WHERE
clause on the end. Since I have to parse the pre- existing
SQL that much, I thought I'd look for a procedure that
breaks it into all its parts. Like I said, I've seen such
functionality in other languages. Sometimes it's bundled
in with the language itself.
-----Original Message-----
I do not "parse" the string so much as build it on the
fly in code.
For example:
Dim strSQL As String
Dim strWhere As String
strWhere = Forms![FormName]![txtWhere]
strSQL = "SELECT field1 "
strSQL = strSQL & "FROM MyTable "
strSQL = strSQL & "WHERE " & strWhere
strSQL = strSQL & "ORDER BY field1 "
As you can see if the user enters a valid Where clause in
the form the SQL
command runs correctly.
There are many variations on this theme.
One thing to be very careful about is a SQL Injection
attack. (A malacious
user could enter in some very naughty SQL that is "valid"
but wreaks havoc
on your database.)
--
Joe Fallon
Access MVP
message
I need a routine that will break apart the clauses in a
SQL statement, so I can modify some and put it back to
gether. (E.g., I need to insert a WHERE clause between
the SELECT and ORDER BY). This isn't rocket science,
but
my experience has been that it's common enough that
routines exist in shareware, etc., in other languages.
Access is so rich in web sites with free code, but I
haven't found such a routine. Does anyone know where I
could find one before I re-invent the wheel and write my
own?
.
.
