[AccessD] Modifying a query

Because the query language is SQL, not DAO. 


Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
- Beach Access Software
Sent: Wednesday, June 07, 2006 11:06 AM
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