Adding total fields from subreports - empty fields cause error 1

[Sashanan]

I've posted this question a few days ago but I think I didn't properly describe it. Here goes again, giving my best amateur assessment of the situation.

I have a report with four subreports. Each of these subreports is fed by a different query, generating any number of numeric records and having a 'total' field adding up the total. The main report has a "grand total" field which is supposed to add up the four individual totals from each subreport.

All works fine as long as each of the four subreports actually has records, however the queries are fed by user input and frequenly, only two or three of the subreports actually have any records. On the ones that don't, the total field won't even appear. Which is fine with me layoutwise, but this seems to be causing trouble when trying to add the fields up.

When at least one of the four subreports generates no 'total' value, the calculation in the 'grand total' field fails. I've been recommended to try the Nz() function to convert null values to 0 for the purposes of the calculation, but it doesn't help at all. I suspect this is because I'm not actually dealing with records where the column I'm adding up has a null value, but with the absence of any records matching the query criteria.

Explaining it graphically:

SUBREP1
10
4
5

Total1: 19

SUBREP2
[no records]
[total field isn't shown]

SUBREP3
5

Total3: 5

SUBREP4
4
7

Total4: 11

MAINREP
Grandtotal: Total1+Total2+Total3+Total4

Again, the calculation works just fine as long as all four of those totals exist, so the references to the subreports are in order. But as soon as one of them doesn't exist because it wasn't fed any records, the calculation fails.

What I want is that if a total doesn't exist, a 0 is taken for it instead. I've tried putting Nz() around each part of the calculation in the grandtotal field, as well as on the individual total fields on the subreports. Neither did me any good.

Can anybody help me out here? It's been stalling me for days now, and I can't put off finishing this report much longer.
"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[hymn]

Check out

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acfctNZ.asp

hope this helps

 

[maca9]

Sashanan

I have the EXACT same problem and have tried using the Nz function too.

Have you managed to fix this??

I need to find a solution.

Cheers

 

[Sashanan]

What I have eventually done to solve it was to write a VBA function to do the calculation instead, and had the "grand total" field refer to that. The calculation in the function was basically:

GrandTotal = SubTotal1
GrandTotal = GrandTotal + SubTotal2
GrandTotal = GrandTotal + SubTotal3
(etc.)

The key command in this function turned out to be "On Error Resume Next". This way, whenever the calculation came to adding a field that couldn't be added (because the field wasn't there), it would just go on without adding it: i.e. it would count it as 0, exactly what I wanted to achieve.

No idea if this was the best way to solve it but it got the job done for me, and it might for you as well.
"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[djjesp]

Try something like:
Grandtotal: IIf(isNull(Total1;0;Total1)) + IIf(isNull(Total2;0;Total2))+ IIf(isNull(Total3;0;Total3))+ IIf(isNull(Total4;0;Total4))
or
Grandtotal: IIf(isEmpty(Total1;0;Total1)) + IIf(isNull(Total2;0;Total2))+ IIf(isNull(Total3;0;Total3))+ IIf(isNull(Total4;0;Total4))

Jesp

 

[jimjawn]

Sashanan:

I'm trying to do EXACTLY what you're doing, but I'm having mad difficult problems. I have a report that has 2 subqueries that will return different subtotals, I need to add those subtotals together into the main form into a 'totals' field. I cannot for the life of me, figure out how to reference the subtotal controls on the subforms. I keep getting the #name? error or the dialog box that pops up and requests the necessary values. Could you perhaps point me in the right direction for getting the total.

Here are the names:

rptInvoice 'Main Report
rptInvoiceShifts 'Sub Report 1
rptInvoiceAdjustments 'Sub Report 2

ShiftsSubtotal - 'Control Name on Sub 1
AdjustmentsSubtotal - 'Control Name on Sub 2
txtTotal - 'Control Name On Main Form

How would I have to name in order to get values, ie:

rptInvoice.txtTotal = rptInvoiceShifts.ShiftsSubtotal + rptInvoiceAdjustments.AdjustmentsSubtotal?

Any Input would be greately appreciated, thanks.

Jim Jawn

 

[Sashanan]

Jim,

The proper way to refer to the fields in the subreports you name would be:

[rptInvoiceShifts].Report!ShiftsSubtotal + [rptInvoiceAdjustments].Report!AdjustmentsSubTotal

I could swear I saw a FAQ here on Tek-Tips a while back which had a nice list of how to reference to fields from a main report to a subreport and the other way around, but I can't find it anymore. May have been somewhere else.


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[danvlas]

Try using the IsError function:

IIf(IsError(Expression), "There is an error", Expression)

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[jimjawn]

Perfect! Thanks a ton man, I appreciate it!