Charlotte Foust
cfoust at infostatsystems.com
Wed Jun 7 15:19:25 CDT 2006
That's the only way to pass criteria in through code other than modifying the SQL to include a where clause. The problem is that the parameters are part of the query SQL. You could add them in code by modifying the SQL but then .... :o< Otherwise, they have to already be there and the criteria have to already be set in the where clause. 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 12:43 PM To: Access Developers discussion and problem solving Subject: Re: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com