[AccessD] Modifying a query

Rocky Smolin - Beach Access Software bchacc at san.rr.com
Wed Jun 7 13:03:52 CDT 2006


A.D:

Thanks for your reply.  That's what I'm going to do.  Put all the SQL 
into CBF, and pull the criterion from a text box.  Actually, as I think 
about it, there's really no reason to store the query at that point.  
Just db.execute it.

Best,

Rocky


A.D.TEJPAL wrote:
> 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
>   

-- 
Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com





More information about the AccessD mailing list