If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Filter query result without presenting the filtering field
My Subform1 is based on qryPayments and presents the Mainform's selected
client' payments sorted by dates. It's Record Source: SELECT [qryPayments].ClientID, [qryPayments].Payment, [qryPayments].CurrencyID, [qryPayments].Date FROM [qryPayments] ORDER BY [qryPayments].Date; The Mainform has unbound cboDateRange based on tblDateRanges consisting of date-range' values ("Between Date() And DateAdd("m",1,Date())" etc.). It applies on the Subform1 with AfterUpdate event: Dim F As Form Set F = Forms![Mainform]![Subform1].Form F.Filter = "[qryPayments].Date" & Me.cboDateRange.Column(2) F.FilterOn = True My Subform2 presents the same info only it summarizes the payments for each currency. It's Record Source is: SELECT [qryPayments].ClientID, Sum([qryPayments].Payment) AS SumOfPayments, [qryPayments].CurrencyID FROM [qryPayments] GROUP BY [qryPayments].ClientID, [qryPayments].CurrencyID; I want to apply the same filter on the Subform2 as well, but it means including the field Date in the latter' underlying query, wich breaks the grouping by currency (presents as many rows for each currency as dates listed). I've tried to avoid it by setting Where instead of Group By in the Date' Total row, but got error msg "Can't display the field with Where in the Total row". I can uncheck the Show check box and still use the Date field with manual criteria (it will apply the criteria even without showing the field), but when applying cboDateRange, it won't find the field Date. How can I apply the daterange filter while presenting the data grouped only by Currency? |
#2
|
|||
|
|||
Filter query result without presenting the filtering field
On Sat, 7 Feb 2009 15:49:01 -0800, Mishanya
wrote: I want to apply the same filter on the Subform2 as well, but it means including the field Date in the latter' underlying query, wich breaks the grouping by currency (presents as many rows for each currency as dates listed). I've tried to avoid it by setting Where instead of Group By in the Date' Total row, but got error msg "Can't display the field with Where in the Total row". I can uncheck the Show check box and still use the Date field with manual criteria (it will apply the criteria even without showing the field), but when applying cboDateRange, it won't find the field Date. How can I apply the daterange filter while presenting the data grouped only by Currency? It should work just the same with a parameter as with a literal date: it should indeed have the Show button unchecked (you don't want to either group by or see the date). Post the SQL of the query and indicate which table/field contains the date field. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Filter query result without presenting the filtering field
Hi John
As posted above: SELECT [qryPayments].ClientID, Sum([qryPayments].Payment) AS SumOfPayments, [qryPayments].CurrencyID FROM [qryPayments] GROUP BY [qryPayments].ClientID, [qryPayments].CurrencyID; When I add the field Date from qryPayments and set the Total row on Where with unchecked Show box and no criteria, the SQL does not change. When I close the query (saving it) and then reopen it - the Date field disappears the only way I've found to keep it in without checking the Show box is to put some criteria (like 0). Executing then cboDateRange: Dim D As Form Set D = Forms![Mainform]![Subform2].Form D.Filter = "[qryPayments].Date" & Me.cboDateRange.Column(2) D.FilterOn = True pops up Enter Parameter Value box requiring to put in value for qryPayments.Date "John W. Vinson" wrote: On Sat, 7 Feb 2009 15:49:01 -0800, Mishanya wrote: I want to apply the same filter on the Subform2 as well, but it means including the field Date in the latter' underlying query, wich breaks the grouping by currency (presents as many rows for each currency as dates listed). I've tried to avoid it by setting Where instead of Group By in the Date' Total row, but got error msg "Can't display the field with Where in the Total row". I can uncheck the Show check box and still use the Date field with manual criteria (it will apply the criteria even without showing the field), but when applying cboDateRange, it won't find the field Date. How can I apply the daterange filter while presenting the data grouped only by Currency? It should work just the same with a parameter as with a literal date: it should indeed have the Show button unchecked (you don't want to either group by or see the date). Post the SQL of the query and indicate which table/field contains the date field. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Filter query result without presenting the filtering field
On Sat, 7 Feb 2009 17:12:00 -0800, Mishanya
wrote: Hi John As posted above: SELECT [qryPayments].ClientID, Sum([qryPayments].Payment) AS SumOfPayments, [qryPayments].CurrencyID FROM [qryPayments] GROUP BY [qryPayments].ClientID, [qryPayments].CurrencyID; When I add the field Date from qryPayments and set the Total row on Where with unchecked Show box and no criteria, the SQL does not change. When I close the query (saving it) and then reopen it - the Date field disappears the only way I've found to keep it in without checking the Show box is to put some criteria (like 0). The field will be removed if it's neither shown nor given a criterion - but why not just give it a criterion? Include the field; use the Where totals "operator"; and put the form reference to whatever control (or controls) contain the date criterion (criteria). You will not need to use the form's filter property at all - instead apply the criterion to the query and use the query as the recordsource for the form. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Filter query result without presenting the filtering field
How should I put the parameter:
Forms![Mainform]![cboDateRange] .Column(2) gives an error? "John W. Vinson" wrote: On Sat, 7 Feb 2009 17:12:00 -0800, Mishanya wrote: Hi John As posted above: SELECT [qryPayments].ClientID, Sum([qryPayments].Payment) AS SumOfPayments, [qryPayments].CurrencyID FROM [qryPayments] GROUP BY [qryPayments].ClientID, [qryPayments].CurrencyID; When I add the field Date from qryPayments and set the Total row on Where with unchecked Show box and no criteria, the SQL does not change. When I close the query (saving it) and then reopen it - the Date field disappears the only way I've found to keep it in without checking the Show box is to put some criteria (like 0). The field will be removed if it's neither shown nor given a criterion - but why not just give it a criterion? Include the field; use the Where totals "operator"; and put the form reference to whatever control (or controls) contain the date criterion (criteria). You will not need to use the form's filter property at all - instead apply the criterion to the query and use the query as the recordsource for the form. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Filter query result without presenting the filtering field
On Sat, 7 Feb 2009 18:18:01 -0800, Mishanya
wrote: How should I put the parameter: Forms![Mainform]![cboDateRange] .Column(2) gives an error? I have no idea, because I have no idea what is in that combo, what your criteria should be, or even what specifically you're trying to accomplish. Details please? -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Filter query result without presenting the filtering field
OK
Right now I have Subform with underlying query as follows: SELECT [qryPayments].ClientID, [qryPayments].Payment, [qryPayments].Currency, [qryPayments].Date FROM [qryPayments] In the Mainform I have cboDateRange based on tblDateRanges, where 0 column is Autonumber, 1st is range-description ("Month" etc., this column is shown to user) and 2nd is date-range' values ("Between Date() And DateAdd("m",1,Date())" etc.) wich are applied when chosen. The combo has AfterUpdate event: Dim F As Form Set F = Forms![Mainform]![Subform].Form F.Filter = "[qryPayments].Date" & Me.cboDateRange.Column(2) F.FilterOn = True So, whenever I select the combo value, the recordset of the Subform is filtered with the selected date-range. Now I want to group the Suform by Currency (so sums by each currency would be presented with no dates break-down) but still be able to filter it with the combo. I've tried to put Where in the Date Total row, uncheck its SHow box and set its criteria to Forms![Mainform]![cboDateRange] .Column(2), so the SQL is now: SELECT [qryPayments].ClientID, Sum([qryPayments].Payment) AS SumOfPayment, [qryPayments].Currency FROM [qryPayments] WHERE ((([qryPayments].Date)=Forms![Mainform]![cboDateRange] .Column(2))) GROUP BY [qryPayments].ClientID, [qryPayments].Currency; but ececuting the combo gives error: Undefined function 'Forms![Mainform]![cboDateRange] .Column' in expression. (Error 3085) You entered an SQL expression that includes a Function procedure name that cannot be recognized. Make sure the function exists, that it can be used in SQL expressions, or check the expression to make sure you entered the name correctly. as if it can not understand the Column part. "John W. Vinson" wrote: On Sat, 7 Feb 2009 18:18:01 -0800, Mishanya wrote: How should I put the parameter: Forms![Mainform]![cboDateRange] .Column(2) gives an error? I have no idea, because I have no idea what is in that combo, what your criteria should be, or even what specifically you're trying to accomplish. Details please? -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|