×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Queries and JET SQL > FAQs

Microsoft: Access Queries and JET SQL FAQ

How To

How to Change SQL property of saved query (DAO) by dhookom
faq701-7433
Posted: 27 Jan 11

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
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close