I am attempting to set a filter on a subform using Filter and FilterOn. Below
is the SQL for that subform. If I leave "DISTINCT" out of the SQL, the filter
on the subform will work (no errors) but it does not return the records
correctly. If I add DISTINCT back into the SQL statement then I get an error.
Run-time error '2001' you canceled the previous operation. If I leave the
DISTINCT in the SQL statement and comment out the DCount portion 'CkIt' then
I do not get the error but I also do not get the results I am looking for. Is
there a way to do a SELECT DISTINCT and be able to use DCount also?


CODE
SELECT DISTINCT PQuoteDetails.OrdersID, PQuoteDetails.ProductID, Products.
VendorsID, IIf(IsNull([FundingID]),0,DCount("ProductID","qryRCandProduct",
"FundingID=" & Forms.[frmPQ].[txtFundingID] & " AND RCodeID=" &
[qryReimbursePQ].[RCodeID] & " AND OrdersID=" & Forms.[frmPQ].[OrderID])) AS
CkIt, IIf([FundingID]<>Forms.[frmPQ].[txtFundingID],0,[ReimbursementAmount])
AS RCAmt, qryReimbursePQ.RCodeID, qryReimbursePQ.ReimbursementCode,
qryReimbursePQ.FundingID, qryReimbursePQ.ReimbursementAmount, Products.
ProductDesc, PQuoteDetails.Quantity, Products.PartNumber, Products.
RetailAmount, PQuoteDetails.Note, [Quantity]*[RetailAmount] AS LTotal
FROM (PQuoteDetails LEFT JOIN Products ON PQuoteDetails.ProductID = Products.
ProductID) LEFT JOIN qryReimbursePQ ON Products.RCodeID = qryReimbursePQ.
RCodeID;


Just in case it helps out below is the code behind the cmdButton to filter
the subform
Forms![frmPQ].Requery
Forms![frmPQ]![sfrmPQDetails].Form.RecordSource = "qryRCandProduct" 'Here is
where it points when the error occurs
Forms![frmPQ]![sfrmPQDetails].Form.Filter = "FundingID =" & Forms![frmPQ]!
[txtFundingID] & " Or FundingID Is Null Or CkIt = 0"
Forms![frmPQ]![sfrmPQDetails].Form.FilterOn = True

TIA,
Shane

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1