Filter query result without presenting the filtering field

[Mishanya]

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?

 

[John W. Vinson]

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.

 

[Mishanya]

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]

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.

 

[Mishanya]

How should I put the parameter:
Forms![Mainform]![cboDateRange] .Column(2) gives an error?

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]

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?

 

[Mishanya]

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.

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?