By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,466 Members | 1,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,466 IT Pros & Developers. It's quick & easy.

Parsing out SQL statement

P: n/a
Does anyone know of a good function that will parse out parts of an SQL
statement that is passed to it in seperate variables?

It should be able to parse statements that contain ORDERBY, WHERE,
GROUP, etc.

Thank you,

Brian

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Ummm... no, but that won't stop you from writing your own...

use InStr to find the chunks you want, and then use left, right, mid to
strip out the chunks you want.

What's the point of this, anyway?

Nov 13 '05 #2

P: n/a
I am trying to accomplish this:

Read in an "orginals" query SELECT using a querydef and setting a
variable called strSQL.
Parse out strSQL to different variables, strSQLWhere, strGroupBy,
strOnOrder, etc.

Then add any additional criteria that a users selects based on Yes/No
fields and/or check boxes.

Then resave the query as their own query with their intitals attached
in the queryname.

Any ideas?

Thanks
Brian

Nov 13 '05 #3

P: n/a
BerkshireGuy wrote:
I am trying to accomplish this:

Read in an "orginals" query SELECT using a querydef and setting a
variable called strSQL.
Parse out strSQL to different variables, strSQLWhere, strGroupBy,
strOnOrder, etc.

Then add any additional criteria that a users selects based on Yes/No
fields and/or check boxes.

Then resave the query as their own query with their intitals attached
in the queryname.

Any ideas?


Would be 1000% easier to just have a base query and put a SQL string
together to query on that query.

--
[OO=00=OO]
Nov 13 '05 #4

P: n/a
BerkshireGuy wrote:
I am trying to accomplish this:

Read in an "orginals" query SELECT using a querydef and setting a
variable called strSQL.
Parse out strSQL to different variables, strSQLWhere, strGroupBy,
strOnOrder, etc.

Then add any additional criteria that a users selects based on Yes/No
fields and/or check boxes.

Then resave the query as their own query with their intitals attached
in the queryname.

Any ideas?

Thanks
Brian


What you want is a bit of trivial drudgery. Why not do it yourself? It
certainly is no challenge to anyone who will take a bit of time to read
the help files and do a bit of work. And someone who does this might
actually learn something.
Make your best try. If your effort has problems post it here then, and
ask for help.

Nov 13 '05 #5

P: n/a
"BerkshireGuy" <bd*****@yahoo.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:
I am trying to accomplish this:

Read in an "orginals" query SELECT using a querydef and setting a
variable called strSQL.
Parse out strSQL to different variables, strSQLWhere, strGroupBy,
strOnOrder, etc.

Then add any additional criteria that a users selects based on
Yes/No fields and/or check boxes.

Then resave the query as their own query with their intitals
attached in the queryname.


Well, sounds like a challenge, but it's not too difficult, assuming
you're not going to be mucking around with the FROM part of the SQL
statement.

It would go something like this (air code):

Dim strSQL As String
Dim strTemp as String
Dim lngOrderBy As Long
Dim strOrderBy As String
Dim lngWhere As Long
Dim strWhere As String
Dim strNewSQL As String
Dim strNewQueryName As String
Dim qdf As DAO.QueryDef

' get the SQL string of the existing query
strSQL = CurrentDB().QueryDefs("qryYourQuery")
' parse out the parts of the SQL string
If Left(Len(strSQL)-1) = ";" Then
strTemp = Left(Len(strSQL)-1) ' strip off the trailing ";"
End If
lngOrderBy = Instr(strTemp,"ORDER BY") ' get the start of ORDER BY
strOrderBy = Mid(strTemp, lngOrderBy) ' pull out ORDER BY clause
strTemp = Left(strTemp, lngOrderBy - 1) ' strip off the ORDER BY
lngWhere = Instr(strTemp, "WHERE") ' get the start of the WHERE
strWhere = Mid(strTemp, lngWhere) ' pull out the WHERE clause
' assign the base of the new SQL
strSQLNew = Mid(strTemp, lngWhere - 1)

' parse the WHERE clause to get it ready for alteration
' this parsing assumes the WHERE clause was written and saved with
' maximum verbosity of parentheses as in:
' 1 2 3 3 2 2 3 3 2 1
' WHERE ( ( (field) = value) AND ( (field2) = value2) )
'
If Mid(strWhere, 7, 1) = "(" Then
' take the starting ( out of the WHERE clause
strWhere = Mid(strWhere, "WHERE " & Mid(strWHERE, 7)
' strip the trailing ) from the WHERE clause
strWhere = Left(strWhere, Len(strWhere) - 1)
End If
' add your new criteria to the original WHERE clause
strWhere = strWhere & " AND " & [your new SQL]

' assemble the new SQL string from the parsed/altered parts
strNewSQL = strNewSQL & vbCrLf & strWhere & vbCrLf & strOrderBy

' prepare to save the new QueryDef
' assume user initials are gotten from CurrentUser()
strNewQueryName = "qryYourQuery" & CurrentUser()
' check if the QueryDef already exists
' function ExistsQuery is defined after my signature
If ExistsQuery(strNewQueryName) Then
CurrentDb().QueryDefs.Delete strNewQueryName
End If
Set qdf = CurrentDB.CreateQueryDef(strNewQueryName, strNewSQL)
qdf.Close
Set qdf = Nothing

Now, all that said, I would suggest that it's mostly a waste of
time.

There is really no reason to save a querydef for something that is
dynamically changing all the time. That is, if you're pulling your
criteria that are being used to alter the WHERE clause from a
query-by-form interface, then just write the SQL in code, rather
than parsing it out of saved QueryDef and then saving it back to a
saved QueryDef that's going to be replaced every time the user wants
to perform a new search. Then use the resulting SQL as the
recordsource of a form to display the results to the user.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Public Function ExistsQuery(strQueryName As String, _
Optional db As DAO.Database) As Boolean
Dim bolNoDBPassed As Boolean
Dim qdf As DAO.QueryDef
Dim bolOutput As Boolean

bolNoDBPassed = (db = Nothing)
If bolNoDBPassed Then Set db = CurrentDB()

For Each qdf in db.QueryDefs
bolOutput = (qdf.Name = strQueryName)
If bolOutput Then Exit For
Next qdf

Set qdf = Nothing
if bolNoDBPassed Then Set db = Nothing

ExistsQuery = bolOutput
End Function
Nov 13 '05 #6

P: n/a
Trevor Best <no****@besty.org.uk> wrote in
news:42**********************@news.zen.co.uk:
BerkshireGuy wrote:
I am trying to accomplish this:

Read in an "orginals" query SELECT using a querydef and setting a
variable called strSQL.
Parse out strSQL to different variables, strSQLWhere, strGroupBy,
strOnOrder, etc.

Then add any additional criteria that a users selects based on
Yes/No fields and/or check boxes.

Then resave the query as their own query with their intitals
attached in the queryname.

Any ideas?


Would be 1000% easier to just have a base query and put a SQL
string together to query on that query.


Yes, but that doesn't always work if unless you output all the
fields from the source tables.

I agree, though, that it's something of a waste of time. I wrote out
code to do it, but still wouldn't use it myself. I'd just write the
SQL in code and never worry about the saved querydefs at all. That
would mean I wouldn't have to worry about any pre-existing ORDER BY
and WHERE clauses.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.