Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to Change SQL property of saved query (DAO)

How To

How to Change SQL property of saved query (DAO)

by  dhookom  Posted    (Edited  )
There are times when the easiest method of creating complex queries with multiple filters is to change the SQL property of a saved query. This works well when you need to send form/control parameters to a pass-through query.

Assuming you have a query "qselMyQuery" that needs a couple dates from controls on a form. Your code might look like:
Code: 
Dim strSQL as String
Dim strOldSQL as String
strSQL = "SELECT field1, field2, field3 FROM tblMyTable " & _
    "WHERE OrderDate BETWEEN #" & Me.txtStart & "# AND #" & _
    Me.txtEnd & "# " & _
    "ORDER BY field2, field1 DESC"
strOldSQL = fChangeSQL("qselMyQuery",strSQL)
The function is:
Code: 
Function fChangeSQL(pstrQueryName As String, strSQL As String) As String
[green]'=============================================================
' basQueryDefs.ChangeSQL
'-------------------------------------------------------------
' Purpose : update the SQL property of a saved query
' Copyright: Duane Hookom
' Author : Duane Hookom
' Notes :
'-------------------------------------------------------------
' Parameters
'-----------
' pstrQueryName (String) Name of saved query
' strSQL (String)        SQL Statement
'-------------------------------------------------------------
' Returns: the previous SQL statement
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' 07-09-2001 DKH:
'=============================================================
' End Code Header block[/green]
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs(pstrQueryName)
    fChangeSQL = qd.SQL
    qd.SQL = strSQL
    Set qd = Nothing
    Set db = Nothing
End Function
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top