DCount with multiple criteria in subform

zebrafoot

Member
Local time
Today, 06:24
Joined
May 15, 2020
Messages
75
Good morning,

I'm tying myself up in knots trying to count the number of records that match two criteria to be displayed in a subform. I appreciate that there are similar questions on this forum, but I can't find something that matches what I'm trying to do, hence I've asked here. Thank you for your patience!

This is what I would like to do:

I have a subform which shows the Contracts that the Customer has taken out. The subform is a continuous form.
Each Contract will have a number of Visits.
The Visits can be of a number of different varieties (in person, remote support etc); the type is identified by a value called SupportContractVisitTypeID.

I'd like to display in a control on the subform the number of Visits of each type made within each numbered Contract - I was trying to do something along the lines of this:

=Nz(DCount("*","tblSupportContractVisits","[SupportContractVisitTypeID]=1 AND [SupportContractID] = A_NUMBER_HERE"),0)

Where A_NUMBER_HERE is the number of the SupportContract on that line of the subform.

This works ok if I put a specific contract number in place of A_NUMBER_HERE, but I don't know how to write the code to pull back the contract number for that line of the subform. I feel this is probably straightforward for an expert, however I've always been confused by Access syntax.

Many thanks in advance,
Pete
 
Actually, I think I've answered my own question:

=Nz(DCount("*","tblSupportContractVisits","[SupportContractVisitTypeID]=1 AND [SupportContractID] = txtSupportContractID"),0)

seems to work. Does that look right to you experts?

Pete
 
try to concatenate the txtSupportContractID:

Code:
=DCount("*","tblSupportContractVisits","[SupportContractVisitTypeID]=1 AND [SupportContractID] = " & Nz(txtSupportContractID, 0))
 
Thanks for that, arnelgp.

Will the bit Nz(txtSupportContractID,0) not return a zero when there is no SupportContractID? This won't happen, as there won't be a line in the subform when there is no Support Contract. What I'm after is counting the number of visits, but returning a zero when there haven't been any visits for that Support Contract.

Hope that makes sense.

Pete
 
This won't happen
if this will not happen, try:
Code:
=DCount("*","tblSupportContractVisits","[SupportContractVisitTypeID]=1 AND [SupportContractID] = " & txtSupportContractID)

you don't need Nz() also, since DCount will return 0 or any numeric value (will not return Null).
 

Users who are viewing this thread

Back
Top Bottom