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
'=============================================================
' 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
    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

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

My FAQ Archive

Come Join Us!

Posting Guidelines

Jobs

Microsoft: Access Queries and JET SQL FAQ

How To

CODE

CODE

My Archive

Join Tek-Tips® Today!

Join Tek-Tips^® Today!