A.D.TEJPAL
adtp at airtelbroadband.in
Wed Jun 7 12:55:44 CDT 2006
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