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
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