Dsum Criteria 1

[sterlecki]

On a subform I have a checkbox [ExcludeTest] used to exclude a record. Next to that I have a textbox that counts the records excluded (ie checked) named [numTestsExcluded].

The criteria for the subform is based on a combobox on a mainform [cbo_LeaseNumber]

If I test using a query the SQL reads:

SELECT data_GroupData.LeaseNumber, Sum(data_GroupData.ExcludeTest) AS SumOfExcludeTest
FROM data_GroupData
GROUP BY data_GroupData.LeaseNumber
HAVING (((data_GroupData.LeaseNumber)=[Forms]![frm_TestReview]![cbo_LeaseNumber]) AND ((Sum(data_GroupData.ExcludeTest))=True));

Which works fine

When I try to use Dsum in the text box:

=(-(DSum("[ExcludeTest]","data_GroupData","Forms!frm_TestReview![frm_subfrm_TestDataReview]![ExcludeTest]=true" And "[data_GroupData.LeaseNumber]=" & [Forms]![frm_TestReview]![cbo_LeaseNumber])))

I get a count of all the [ExcludeTest] checkboxes that are checked without it filtering for the LeaseNumber.

Where am I going wrong?

Note: I've added the - sign in order to keep the number positive within the display

 

[ProgramError]

I would firstly filter the form or query the tables with the variation of the algrhythm you shown us in you question.

("[ExcludeTest]","data_GroupData","Forms!frm_TestReview![frm_subfrm_TestDataReview]![ExcludeTest]=true" And "[data_GroupData.LeaseNumber]=" & [Forms]![frm_TestReview]![cbo_LeaseNumber])

then just count the excludetest field or number of records

Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[PHV]

=-DSum("ExcludeTest","data_GroupData","ExcludeTest=True AND LeaseNumber=" & Forms!frm_TestReview!cbo_LeaseNumber)



Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sterlecki]

Hi PHV!

Tried your version but got an error flashing like it was in a loop.

I did see a similar post where you suggested using a saved query. I tried that:

=(-(DSum("[ExcludeTest]","qry_SumofExcludeTest")))

and it worked great, just seems like too much trouble with the saved query.

Any ideas why the error occurred?

 

[PHV]

What about this ?
=Abs(DSum("ExcludeTest","data_GroupData","LeaseNumber=Forms!frm_TestReview!cbo_LeaseNumber"))



Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sterlecki]

Hello PHV,

Works like a charm. Thanks for the 2nd look! A Star for you!