Thanks for your help. Trying to sum participant #s in main report. Participant #s are computed in the subreport.
The main report named zrptAllTrTypesTrainingsByDateDirectPart is grouped by Training Type, and the detail section looks like this and will have several trainings under each training type:
TrainingType (grouped by, header)
TrainingBeginDate CityState txtDirectPartSum [zsubrptAllTrTypesByDateDirectPart].[txtDirectParticipantNum] Instructor
Within the subreport zsubrptAllTrTypesByDateDirectPart,the number of participants is stored in a field named txtDirectParticipantNum. The control source is a query that sums the participants for each training from a table. The participant numbers are entered by groups that attend a specific training, like Teachers (5) + Principals (10) = (15) participants for training X. For the specific training in this example, txtDirectParticipantNum's value would be 15.
I'm trying to sum the number of participants in the main report, in the TrainingType footer.
This is what's working so far...
I'm storing the # of participants on the main report in each detail line in a calculated text box called txtDirectPartSum (which will not be visible).I get the value for this unbound field from the subreport using this function: =IIf([zsubrptAllTrTypesTrainingsByDateDirectPart].[Report].[HasData],Nz([zsubrptAllTrTypesTrainingsByDateDirectPart].[Report].[txtDirectParticipantNum],0),0). This works great. I thought storing the value on the main form would make it easier to sum in the TrainingType footer.
What's not working...summing txtDirectPartSum in the TrainingType footer.
I have an unbound calculated text box named txtSummedDirectPartSum in the TrainingType footer to sum all of the participant numbers in the training type section (all the txtDirectPartSum values). I've tried the following functions inside txtSummedDirectPartSum and can't get any to work.
Code:
=Sum([txtDirectPartSum])
Code:
=Sum([zrptAllTrTypesTrainingsByDateDirectPart].[txtDirectPartSum])
Code:
=Sum(IIf([zsubrptAllTrTypesTrainingsByDateDirectPart].[Report].[HasData],Nz([zsubrptAllTrTypesTrainingsByDateDirectPart].[Report].[txtDirectParticipantNum],0),0))
Code:
=Sum(IIf([zrptAllTrTypesTrainingsByDateDirectPart].[Report].[zsubrptAllTrTypesTrainingsByDateDirectPart].[Report].[HasData],Nz([zsubrptAllTrTypesTrainingsByDateDirectPart].[Report].[txtDirectParticipantNum],0),0))
Code:
=Sum(IIf([zrptAllTrTypesTrainingsByDateDirectPart].[Report].[zsubrptAllTrTypesTrainingsByDateDirectPart].[HasData],Nz([zsubrptAllTrTypesTrainingsByDateDirectPart].[Report].[txtDirectParticipantNum],0),0))
In each case, Access is not recognizing the syntax after =(Sum(, or between =Sum(IIf([ and ,Nz.
fyi: txtDirectPartSum and txtSummedDirectPartSum and txtDirectParticipantNum are formatted as Standard, no decimals.
Thanks again for your help!