Filtering query results on unincluded field

M

Mishanya

I have Subform with underlying query as follows:

SELECT [qryPayments].ClientID, [qryPayments].PaymentSum,
[qryPayments].Currency, [qryPayments].Date
FROM [qryPayments]

In the Mainform I have unbound 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 records 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 can not use the same AfterUpdate routine as it requires the field Date to
be shown in the query (wich causes the result show grouping by currency AND
by dates.
I've tried to work it out with Parameters setting, putting Where in the Date
Total row, unchecking its Show box and setting its criteria to
Forms![Mainform]![cboDateRange] .Column(2), so the SQL is :

SELECT [qryPayments].ClientID, Sum([qryPayments].PaymentSum) AS
SumOfPaymentSum, [qryPayments].Currency
FROM [qryPayments]
WHERE ((([qryPayments].Date)=Forms![Mainform]![cboDateRange] .Column(2)))
GROUP BY [qryPayments].ClientID, [qryPayments].Currency;

but opening theMainform and/or executing 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 recognize the Column part.

What can be done?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top