[AccessD] Modifying a query

Rocky,

    Apparently, you wish to modify a saved query as per desired criteria string. You could consider the simple alternative of setting up a wrapper saved query whose Where clause could be modified as required from time to time. 

    The original query would be a plain one, without any Where clause. This approach should suit most situations unless it is a case of Totals query where criteria needs to be applied before the Group By clause.

    Function Fn_PutQueryFilter() as given below, will create the required wrapper query (or modify suitably if such a query is already existing). It takes the name of original query and the criteria string as its arguments.

    For example, if original query is named "Q_A", a new one called "Q_A_Filtered" will get created, duly incorporating the desired criteria in its Where clause. (Query "Q_A" is a plain one, with no Where clause).  If "Q_A_Filtered" is already existing, it will get suitably modified.

    Typical statement for calling this function is given below:

    Call Fn_PutQueryFilter(OriginalQueryName,  _
                                            DesiredCriteriaString)

Best wishes,
A.D.Tejpal
---------------

======================================
Function Fn_PutQueryFilter(ByVal QueryName As String, _
            Optional ByVal CriteriaString As Variant) As Long
    ' Creates new saved query named QueryName_Filtered
    ' based upon saved query named QueryName and returns
    ' 1 if successful, otherwise 0
    On Error GoTo ExitPoint
    Dim Status As Long, Qst As String
    Dim NewQueryName As String, Rtv As Variant
    Dim db As DAO.Database
    
    Status = 0   ' Default
    NewQueryName = QueryName & "_Filtered"
    If IsMissing(CriteriaString) Or _
                            Len(Nz(CriteriaString, "")) = 0 Then
        Qst = "Select * From " & QueryName & ";"
    Else
        Qst = "Select * From " & QueryName & _
            " Where " & CriteriaString & ";"
    End If
    
    Set db = CurrentDb
    On Error Resume Next
    Rtv = db.QueryDefs(NewQueryName).Name
    If Err.Number = 0 Then
        db.QueryDefs(NewQueryName).SQL = Qst
    Else
        db.CreateQueryDef NewQueryName, Qst
    End If
    Err.Clear
    
    db.QueryDefs.Refresh
    
   Status = 1
   
ExitPoint:
   Fn_PutQueryFilter = Status
   On Error GoTo 0
End Function
======================================

  ----- Original Message ----- 
  From: Rocky Smolin - Beach Access Software 
  To: Access Developers discussion and problem solving 
  Sent: Wednesday, June 07, 2006 05:49
  Subject: [AccessD] Modifying a query


  Dear List:

  Can you modify the criteria in an existing query through code?  Usually I just construct the query as a SQL statement and use db.execute to run it.  But this time I think it would be better to leave the query where it is and modify the criterion based on a selection by the user.  The query is part of a predefined set of queries that were embedded in a macro by the user.

  MTIA,

  Rocky