[AccessD] Modifying a query

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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.