access 2003 docmd.applyfilter - appears as parameter?

[madhouse]

Hi

I have table whose recordsource is:

SELECT Company.CoID, Company.CoName, Company.Type,
CoClassification.ClassifName, Company.Dely_Address1, Company.Dely_TownCity,
Dely_Contact_First_Name, Dely_Contact_Last_Name,
Sum(SalesInvHdr.TotalSalesValue) AS SumOfTotalSalesValue,
Sum(SalesInvHdr.TotalVatAmount) AS SumOfTotalVatAmount,
Sum(SalesInvHdr.PaymentAmount) AS SumOfPaymentAmount
FROM (CoClassification RIGHT JOIN Company ON
CoClassification.ClassificationID=Company.ClassificationID) LEFT JOIN
SalesInvHdr ON Company.CoID=SalesInvHdr.CoID
GROUP BY Company.CoID, Company.CoName, Company.Type,
CoClassification.ClassifName, Company.ClassificationID,
Company.Dely_Address1, Company.Dely_TownCity, Dely_Contact_First_Name,
Dely_Contact_Last_Name;

On the click event of a button in the table's header I can set filter
strings and then use "DoCmd.Applyfilter , strFString" to filter the table's
rows.

Works fine with string such as
strFString="SumOfTotalSalesValue > 0 and Company.CoID < 10".

However with:
strFString="SalesInvHdr.OrderEntryDate < #12/8/2004#" it prompts with "Enter
Parameter Value for SalesInvHdr.OrderEntryDate".

The big Select works fine if this string is edited directly in to it as a
Where clause.

Please help its driving me nuts.

Many thanks in anticipation.

Madhouse

 

[madhouse]

Have just noticed it works if this filter field (SalesInvHdr.OrderEntryDate)
is in the fields returned by the select. Of course this is not necessary with
SQL so does anybody know how to avoid this difficulty. IE is it necessary for
all form filters to be fields returned in the in the form's recordsource?

madhouse

 

[John Vinson]

IE is it necessary for
all form filters to be fields returned in the in the form's recordsource?

Yes. The filter is filtering the recordsource. Fields that aren't in
the recordsource aren't available for filtering.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps