"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
