Counting the fields in a sub sub report that equal E or D

[carlb]

I am trying to count the fields in a sub sub report. They have to
either be E or D So far I have used the command builder to come up with
this

=Count(Reports![Requirements]![Training subreport]![Training
Subsubreport].Report![Date-Due]="D")

And

=Count(Reports![Requirements]![Training subreport]![Training
Subsubreport].Report![Date-Due]="E")

The data is calculated in the query with a switch statement. That
works. Any help would be appriciated

 

[Al Camp]

Carl,
In the query begind your sub sub report, you can create a calculated column...
IsDateDueDE : IIF([Date-Due] = "D" OR [Date-Due] = "E", 1, 0)
then sum DateDue.
If that sum field were called SumDateDueDE, you could refer to it easily from anywhere
else on the report.

A very minor point... avoid spaces in field names, then you don't have to bracket
every object name. And, I'm a bit nervous about the using a minus sign in a field name
(Date-Due). You've bracketed properly, so it shouldn't be a problem. It's more a matter
of style than anything serious...
ex. TrainingSubReport, or DateDue, or Date_Due...

 

[RoyVidar]

(e-mail address removed) wrote in message

I am trying to count the fields in a sub sub report. They have to
either be E or D So far I have used the command builder to come up
with this

=Count(Reports![Requirements]![Training subreport]![Training
Subsubreport].Report![Date-Due]="D")

And

=Count(Reports![Requirements]![Training subreport]![Training
Subsubreport].Report![Date-Due]="E")

The data is calculated in the query with a switch statement. That
works. Any help would be appriciated

But what is the problem? Does it display #Name, #Value, 0 ...?

I haven't tested this for subreports, but perhaps try something like
this:

=abs(sum(Reports![Requirements]![Training subreport].Report![Training
Subsubreport].Report![Date-Due] like "[DE]"))

 

[carlb]

Thank you for the help. I can't seem to access the information in the
query. to test for the information I used this VBA code

Me.DC = Me.Training_subreport.Employee_Training_Subsubreport.IsDueDateD

I have a sum on the query that sums up the total of D

How do I acces the query and get the total sums from all of the sub-sub
reports for the grouped section of the main report?

 

[carlb]

As an update I have tried this in the command builder of the main form
control that I am trying to create the sum in

=Sum(Reports!Requirements![Training subreport]![Employee-Training
Subsubreport].Report!IsDateDueD)

When I do this is asks me for the value for:

Reports!Requirements![Training subreport]![Employee-Training
Subsubreport].Report!IsDateDueD