[AccessD] Modifying a query

Thought about it?  I didn't even know about it!  That's the kind of 
thing I would have suspected existed.  But are you actually modifying 
and saving the query or just modifying it for purposes of opening the 
recordset?

Rocky


Hale, Jim wrote:
> Have you thought about using the parameters collection?
> Jim Hale
>
>  Set dbs = CurrentDb
>     Set qdfTemp = dbs.QueryDefs(strQryname)
>     If Not strParameter = "" Then qdfTemp.Parameters(strParameter) =
> strCriteria
>     Set rsRecset = qdfTemp.OpenRecordset()
>
> -----Original Message-----
> From: Rocky Smolin - Beach Access Software [mailto:bchacc at san.rr.com]
> Sent: Wednesday, June 07, 2006 1:06 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Modifying a query
>
>
> P.S.  However, I'm kind of surprised that there's no object model with 
> properties and methods like there is for tables so you could just write 
> something like qry.Field(n).Criteria = ">= xxx".  Why should that be?
>
> 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