SynNeed to account for null values in subreports

  • Thread starter Thread starter scottyboyb
  • Start date Start date
S

scottyboyb

I have a report that has 3 subreports in the detail section. Each subreport
has the following data fields.
txtWorkTotal - field in Invoice Time Subreport subreport detail section
txtExpenseTotal - field in Invoice Expense Subreport subreport detail section
txtPaymentTotal - field in Invoice Payments Subreport subreport detail section

Fields in subreport report footers total above fields. These work as long as
each subreport has data:
=Sum([BillableHours]*[BillingRate]) in time subreport
=Sum([ExpenseAmount]) in expense subreport
=Sum([PaymentAmount]) in payment subreport

After the first two subreports there is a subtotal field in the detail
section on the main report. Again this works if there is data in all 3
subreports.
=([Invoice Time Subreport].Report![txtWorkTotal]+[Invoice Expense
Subreport].Report![txtExpenseTotal])

Then comes the 3rd subreport. Again all is well with data.

Finally, there is a grand total field on the main report detail section for
all three subreports. Again fine as long as has data in all 3 subreports.
Grand total Invoice report
=([Invoice Time Subreport].Report![txtWorkTotal]+[Invoice Expense
Subreport].Report![txtExpenseTotal]-[Invoice Payments
Subreport].Report![txtPaymentTotal])

Herer is my attempt to deal with null values in subreport data
=Sum(IIf([BillableHours]*[BillingRate].HasData=True,[BillableHours]*[BillingRate],0))
=Sum(IIf([ExpenseAmount].HasData=True,[ExpenseAmount],0))
=Sum(IIf([PaymentAmount].HasData=True,[PaymentAmount],0))

With this, I get all zeros if there is data in all 3 subreports or else I
get #error in the main report subtotal and main report grand total and the
empty subreports are invisble. Also, no matter if there is data or not, with
this last HasData attempt, I get a parameter box for each =sum(IIf(. . . that
says
"Enter Parameter Value: BillingRate.HasData"
"Enter Parameter Value: ExpenseAmount.HasData"
"Enter Parameter Value: PaymentAmount.HasData"

I think my syntax is wrong, but I can't figure out where. I have googled
this and everything I read says this is right. Unless I am misreading. Anyone
have any thoughts? I'm stumped.

I hope this is clear. It is quite an exercise trying to describe this.

Best,
Scott B
 
Looking at just:
=([Invoice Time Subreport].Report![txtWorkTotal]+[Invoice Expense
Subreport].Report![txtExpenseTotal])

This should be replaced with:
=(IIf([Invoice Time Subreport].Report.HasData,
[Invoice Time Subreport].Report![txtWorkTotal],0) +
IIf([Invoice Expense Subreport].Report.HasData,
[Invoice Expense Subreport].Report![txtExpenseTotal],0))

The HasData always follows "Report."
 
Duane,

First many thanks for the help. After our long series of posts last week, I
sat down and worked through all of it and out came a working invoice with
just the exception of the problem you solved tonight. So thank you again for
yor patience and perserverance on both occassions. The post of today @ 3:22
your time was spot on. It worked like a charm. There is joy in invoice land
tonight.

Best,
Scott B

Duane Hookom said:
Looking at just:
=([Invoice Time Subreport].Report![txtWorkTotal]+[Invoice Expense
Subreport].Report![txtExpenseTotal])

This should be replaced with:
=(IIf([Invoice Time Subreport].Report.HasData,
[Invoice Time Subreport].Report![txtWorkTotal],0) +
IIf([Invoice Expense Subreport].Report.HasData,
[Invoice Expense Subreport].Report![txtExpenseTotal],0))

The HasData always follows "Report."
--
Duane Hookom
Microsoft Access MVP


scottyboyb said:
I have a report that has 3 subreports in the detail section. Each subreport
has the following data fields.
txtWorkTotal - field in Invoice Time Subreport subreport detail section
txtExpenseTotal - field in Invoice Expense Subreport subreport detail section
txtPaymentTotal - field in Invoice Payments Subreport subreport detail section

Fields in subreport report footers total above fields. These work as long as
each subreport has data:
=Sum([BillableHours]*[BillingRate]) in time subreport
=Sum([ExpenseAmount]) in expense subreport
=Sum([PaymentAmount]) in payment subreport

After the first two subreports there is a subtotal field in the detail
section on the main report. Again this works if there is data in all 3
subreports.
=([Invoice Time Subreport].Report![txtWorkTotal]+[Invoice Expense
Subreport].Report![txtExpenseTotal])

Then comes the 3rd subreport. Again all is well with data.

Finally, there is a grand total field on the main report detail section for
all three subreports. Again fine as long as has data in all 3 subreports.
Grand total Invoice report
=([Invoice Time Subreport].Report![txtWorkTotal]+[Invoice Expense
Subreport].Report![txtExpenseTotal]-[Invoice Payments
Subreport].Report![txtPaymentTotal])

Herer is my attempt to deal with null values in subreport data.
=Sum(IIf([BillableHours]*[BillingRate].HasData=True,[BillableHours]*[BillingRate],0))
=Sum(IIf([ExpenseAmount].HasData=True,[ExpenseAmount],0))
=Sum(IIf([PaymentAmount].HasData=True,[PaymentAmount],0))

With this, I get all zeros if there is data in all 3 subreports or else I
get #error in the main report subtotal and main report grand total and the
empty subreports are invisble. Also, no matter if there is data or not, with
this last HasData attempt, I get a parameter box for each =sum(IIf(. . . that
says
"Enter Parameter Value: BillingRate.HasData"
"Enter Parameter Value: ExpenseAmount.HasData"
"Enter Parameter Value: PaymentAmount.HasData"

I think my syntax is wrong, but I can't figure out where. I have googled
this and everything I read says this is right. Unless I am misreading. Anyone
have any thoughts? I'm stumped.

I hope this is clear. It is quite an exercise trying to describe this.

Best,
Scott B
Click to expand...
Click to expand...
 
Back
Top