Errors in Report total fields

[scottyboyb]

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly
Billings]
Changed to this when I added the payments subreport:
=sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total
Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments])
***Both Work perfectly!***

Time Subreport Total Billed field. . .
was & is:
[BillableHours]*[BillingRate]
***Works perfectly!***

Problems are in the following fields:
Expenses Subreport Expense Amount field . . .
was:
Forms![Print Invoice]![Total Expenses]
changed to:
iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0)
***Get this error - #Name?***
if changed to:
=sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Print Invoice!Total
Expenses.HasData

Payment Subreport Payment Amount field . . .
was:
Forms![Clients]![Clients Subform]![Total Payments]
changed to:
=iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)
***#Name?***
=sum(iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Clients!Clients Subform!Total

Many thanks,
Scott B

 

[scottyboyb]

Sorry, should have mentioned. Access 2000 and wndows xp sp3.
Thanks,
Scott B

 

[Duane Hookom]

Forms don't "have" data. They generally pull data from tables. Why are you
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly
Billings]
Changed to this when I added the payments subreport:
=sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total
Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments])
***Both Work perfectly!***

Time Subreport Total Billed field. . .
was & is:
[BillableHours]*[BillingRate]
***Works perfectly!***

Problems are in the following fields:
Expenses Subreport Expense Amount field . . .
was:
Forms![Print Invoice]![Total Expenses]
changed to:
iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0)
***Get this error - #Name?***
if changed to:
=sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Print Invoice!Total
Expenses.HasData

Payment Subreport Payment Amount field . . .
was:
Forms![Clients]![Clients Subform]![Total Payments]
changed to:
=iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)
***#Name?***
=sum(iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Clients!Clients Subform!Total

Many thanks,
Scott B

 

[scottyboyb]

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly
Billings]
Changed to this when I added the payments subreport:
=sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total
Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments])
***Both Work perfectly!***

Time Subreport Total Billed field. . .
was & is:
[BillableHours]*[BillingRate]
***Works perfectly!***

Problems are in the following fields:
Expenses Subreport Expense Amount field . . .
was:
Forms![Print Invoice]![Total Expenses]
changed to:
iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0)
***Get this error - #Name?***
if changed to:
=sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Print Invoice!Total
Expenses.HasData

Payment Subreport Payment Amount field . . .
was:
Forms![Clients]![Clients Subform]![Total Payments]
changed to:
=iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)
***#Name?***
=sum(iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Clients!Clients Subform!Total

Many thanks,
Scott B

 

[scottyboyb]

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly
Billings]
Changed to this when I added the payments subreport:
=sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total
Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments])
***Both Work perfectly!***

Time Subreport Total Billed field. . .
was & is:
[BillableHours]*[BillingRate]
***Works perfectly!***

Problems are in the following fields:
Expenses Subreport Expense Amount field . . .
was:
Forms![Print Invoice]![Total Expenses]
changed to:
iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0)
***Get this error - #Name?***
if changed to:
=sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Print Invoice!Total
Expenses.HasData

Payment Subreport Payment Amount field . . .
was:
Forms![Clients]![Clients Subform]![Total Payments]
changed to:
=iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)
***#Name?***
=sum(iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Clients!Clients Subform!Total

Many thanks,
Scott B

 

[Duane Hookom]

Since you are actually referencing subreports and not subforms, you should be
able to grab a total or 0 from a subreport with and expression like:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)
I don't know why the MS Template used Reports!MainReportName...
--
Duane Hookom
Microsoft Access MVP

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP


:

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly
Billings]
Changed to this when I added the payments subreport:
=sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total
Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments])
***Both Work perfectly!***

Time Subreport Total Billed field. . .
was & is:
[BillableHours]*[BillingRate]
***Works perfectly!***

Problems are in the following fields:
Expenses Subreport Expense Amount field . . .
was:
Forms![Print Invoice]![Total Expenses]
changed to:
iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0)
***Get this error - #Name?***
if changed to:
=sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Print Invoice!Total
Expenses.HasData

Payment Subreport Payment Amount field . . .
was:
Forms![Clients]![Clients Subform]![Total Payments]
changed to:
=iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)
***#Name?***
=sum(iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Clients!Clients Subform!Total

Many thanks,
Scott B

 

[scottyboyb]

Duane,

Thanks. I put both of these variations in the subreport total fields. I get
#Name? errors in the subreport total fields and #Error errors in both the
main form subtotal and grand total fields in both instances.

=IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense
Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment
Subreport]. Invoice.PaymentTotal,0)

=IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice
Expense Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice
Payment Subreport]. Invoice.PaymentTotal,0)

I am VERY confused! I really appreciate the help.

Best,
Scott B

Since you are actually referencing subreports and not subforms, you should be
able to grab a total or 0 from a subreport with and expression like:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)
I don't know why the MS Template used Reports!MainReportName...
--
Duane Hookom
Microsoft Access MVP

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t




:

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP


:

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly
Billings]
Changed to this when I added the payments subreport:
=sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total
Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments])
***Both Work perfectly!***

Time Subreport Total Billed field. . .
was & is:
[BillableHours]*[BillingRate]
***Works perfectly!***

Problems are in the following fields:
Expenses Subreport Expense Amount field . . .
was:
Forms![Print Invoice]![Total Expenses]
changed to:
iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0)
***Get this error - #Name?***
if changed to:
=sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Print Invoice!Total
Expenses.HasData

Payment Subreport Payment Amount field . . .
was:
Forms![Clients]![Clients Subform]![Total Payments]
changed to:
=iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)
***#Name?***
=sum(iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Clients!Clients Subform!Total

Many thanks,
Scott B

 

[Duane Hookom]

You should have the word "Report" in your expressions. "Subreport" is not to
be included unless you have something in your reports named "subreport".

=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

The above on your main report will reference a subreport control named
"subrptControlName". This is the name of the control, not necessarily the
Source Object. "Report" is the same in all expressions. It references the
Report contained in subrptControlName. txtTotal is the name of the text box
on the subreport. This is usually in the subreport's footer section.

--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks. I put both of these variations in the subreport total fields. I get
#Name? errors in the subreport total fields and #Error errors in both the
main form subtotal and grand total fields in both instances.

=IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense
Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment
Subreport]. Invoice.PaymentTotal,0)

=IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice
Expense Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice
Payment Subreport]. Invoice.PaymentTotal,0)

I am VERY confused! I really appreciate the help.

Best,
Scott B

Since you are actually referencing subreports and not subforms, you should be
able to grab a total or 0 from a subreport with and expression like:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)
I don't know why the MS Template used Reports!MainReportName...
--
Duane Hookom
Microsoft Access MVP

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

:

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t




:

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP


:

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly
Billings]
Changed to this when I added the payments subreport:
=sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total
Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments])
***Both Work perfectly!***

Time Subreport Total Billed field. . .
was & is:
[BillableHours]*[BillingRate]
***Works perfectly!***

Problems are in the following fields:
Expenses Subreport Expense Amount field . . .
was:
Forms![Print Invoice]![Total Expenses]
changed to:
iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0)
***Get this error - #Name?***
if changed to:
=sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Print Invoice!Total
Expenses.HasData

Payment Subreport Payment Amount field . . .
was:
Forms![Clients]![Clients Subform]![Total Payments]
changed to:
=iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)
***#Name?***
=sum(iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Clients!Clients Subform!Total

Many thanks,
Scott B

 

[scottyboyb]

OK. I am still unclear about subrptControlName. Is that the subreport's name?
Or something on the subreport? I cannot find a listing on the properties of
the subreport or the main report called control name. I am sorry to be so
dense. This ring a very distant bell, but I seem deaf today.

Best,
Scott B

You should have the word "Report" in your expressions. "Subreport" is not to
be included unless you have something in your reports named "subreport".

=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

The above on your main report will reference a subreport control named
"subrptControlName". This is the name of the control, not necessarily the
Source Object. "Report" is the same in all expressions. It references the
Report contained in subrptControlName. txtTotal is the name of the text box
on the subreport. This is usually in the subreport's footer section.

--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks. I put both of these variations in the subreport total fields. I get
#Name? errors in the subreport total fields and #Error errors in both the
main form subtotal and grand total fields in both instances.

=IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense
Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment
Subreport]. Invoice.PaymentTotal,0)

=IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice
Expense Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice
Payment Subreport]. Invoice.PaymentTotal,0)

I am VERY confused! I really appreciate the help.

Best,
Scott B

Since you are actually referencing subreports and not subforms, you should be
able to grab a total or 0 from a subreport with and expression like:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)
I don't know why the MS Template used Reports!MainReportName...
--
Duane Hookom
Microsoft Access MVP


:

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

:

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t




:

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP


:

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly
Billings]
Changed to this when I added the payments subreport:
=sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total
Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments])
***Both Work perfectly!***

Time Subreport Total Billed field. . .
was & is:
[BillableHours]*[BillingRate]
***Works perfectly!***

Problems are in the following fields:
Expenses Subreport Expense Amount field . . .
was:
Forms![Print Invoice]![Total Expenses]
changed to:
iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0)
***Get this error - #Name?***
if changed to:
=sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Print Invoice!Total
Expenses.HasData

Payment Subreport Payment Amount field . . .
was:
Forms![Clients]![Clients Subform]![Total Payments]
changed to:
=iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)
***#Name?***
=sum(iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Clients!Clients Subform!Total

Many thanks,
Scott B

 

[Duane Hookom]

This stuff isn't easy to understand or explain

A main report can have controls on it. These controls are typical text
boxes, labels, rectangles, etc. One type of control you can add to a report
is a subreport control. The subreport CONTROL has a couple important
properties:

Name: the name of the control which you can leave or edit
Source Object: the name of the subreport from the database window

Typically the Name and Source Object are the same but not always.

When I state "subrptControlName", I am referring to the Name property, not
the Source Object. In these expressions, the Source Object is not
significant. When I want to refer to the actual report contained in the
subreport control I use:
"subrptControlName.Report"
When I want to reference a text box (txtTotal) on the subreport, I use:
=subrptControlName.Report.txtTotal
The above will not display a 0 if the subreport doesn't have any data.
That's why we extend the expression to use the HasData property of the report:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

--
Duane Hookom
Microsoft Access MVP

OK. I am still unclear about subrptControlName. Is that the subreport's name?
Or something on the subreport? I cannot find a listing on the properties of
the subreport or the main report called control name. I am sorry to be so
dense. This ring a very distant bell, but I seem deaf today.

Best,
Scott B

You should have the word "Report" in your expressions. "Subreport" is not to
be included unless you have something in your reports named "subreport".

=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

The above on your main report will reference a subreport control named
"subrptControlName". This is the name of the control, not necessarily the
Source Object. "Report" is the same in all expressions. It references the
Report contained in subrptControlName. txtTotal is the name of the text box
on the subreport. This is usually in the subreport's footer section.

--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks. I put both of these variations in the subreport total fields. I get
#Name? errors in the subreport total fields and #Error errors in both the
main form subtotal and grand total fields in both instances.

=IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense
Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment
Subreport]. Invoice.PaymentTotal,0)

=IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice
Expense Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice
Payment Subreport]. Invoice.PaymentTotal,0)

I am VERY confused! I really appreciate the help.

Best,
Scott B

:

Since you are actually referencing subreports and not subforms, you should be
able to grab a total or 0 from a subreport with and expression like:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)
I don't know why the MS Template used Reports!MainReportName...
--
Duane Hookom
Microsoft Access MVP


:

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

:

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t




:

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP


:

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly
Billings]
Changed to this when I added the payments subreport:
=sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total
Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments])
***Both Work perfectly!***

Time Subreport Total Billed field. . .
was & is:
[BillableHours]*[BillingRate]
***Works perfectly!***

Problems are in the following fields:
Expenses Subreport Expense Amount field . . .
was:
Forms![Print Invoice]![Total Expenses]
changed to:
iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0)
***Get this error - #Name?***
if changed to:
=sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Print Invoice!Total
Expenses.HasData

Payment Subreport Payment Amount field . . .
was:
Forms![Clients]![Clients Subform]![Total Payments]
changed to:
=iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)
***#Name?***
=sum(iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Clients!Clients Subform!Total

Many thanks,
Scott B

 

[scottyboyb]

Boy you ain't kidding!

Is that the name on the other tab if I right click the subforom while in the
main form design mode and bring up properties? If so it is "Invoice Expense
Subreport" for the expense subreport. The source object is "Report.Invoice
Expense Subreport". Neither works in the expression.

Thanks,
Scott B

This stuff isn't easy to understand or explain

A main report can have controls on it. These controls are typical text
boxes, labels, rectangles, etc. One type of control you can add to a report
is a subreport control. The subreport CONTROL has a couple important
properties:

Name: the name of the control which you can leave or edit
Source Object: the name of the subreport from the database window

Typically the Name and Source Object are the same but not always.

When I state "subrptControlName", I am referring to the Name property, not
the Source Object. In these expressions, the Source Object is not
significant. When I want to refer to the actual report contained in the
subreport control I use:
"subrptControlName.Report"
When I want to reference a text box (txtTotal) on the subreport, I use:
=subrptControlName.Report.txtTotal
The above will not display a 0 if the subreport doesn't have any data.
That's why we extend the expression to use the HasData property of the report:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

--
Duane Hookom
Microsoft Access MVP

OK. I am still unclear about subrptControlName. Is that the subreport's name?
Or something on the subreport? I cannot find a listing on the properties of
the subreport or the main report called control name. I am sorry to be so
dense. This ring a very distant bell, but I seem deaf today.

Best,
Scott B

You should have the word "Report" in your expressions. "Subreport" is not to
be included unless you have something in your reports named "subreport".

=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

The above on your main report will reference a subreport control named
"subrptControlName". This is the name of the control, not necessarily the
Source Object. "Report" is the same in all expressions. It references the
Report contained in subrptControlName. txtTotal is the name of the text box
on the subreport. This is usually in the subreport's footer section.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

Thanks. I put both of these variations in the subreport total fields. I get
#Name? errors in the subreport total fields and #Error errors in both the
main form subtotal and grand total fields in both instances.

=IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense
Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment
Subreport]. Invoice.PaymentTotal,0)

=IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice
Expense Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice
Payment Subreport]. Invoice.PaymentTotal,0)

I am VERY confused! I really appreciate the help.

Best,
Scott B

:

Since you are actually referencing subreports and not subforms, you should be
able to grab a total or 0 from a subreport with and expression like:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)
I don't know why the MS Template used Reports!MainReportName...
--
Duane Hookom
Microsoft Access MVP


:

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

:

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t




:

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP


:

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly
Billings]
Changed to this when I added the payments subreport:
=sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total
Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments])
***Both Work perfectly!***

Time Subreport Total Billed field. . .
was & is:
[BillableHours]*[BillingRate]
***Works perfectly!***

Problems are in the following fields:
Expenses Subreport Expense Amount field . . .
was:
Forms![Print Invoice]![Total Expenses]
changed to:
iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0)
***Get this error - #Name?***
if changed to:
=sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Print Invoice!Total
Expenses.HasData

Payment Subreport Payment Amount field . . .
was:
Forms![Clients]![Clients Subform]![Total Payments]
changed to:
=iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)
***#Name?***
=sum(iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Clients!Clients Subform!Total

Many thanks,
Scott B

 

[Duane Hookom]

The properties I am referring to are the properties of the subreport control
on the main report. I'm not sure why you use "form" in your replies since I
expect you are dealing only with "report" objects.

Please provide the actual expressions you are attempting to use. I should
also add that your text box on the main report that reference the subreport
should all be in the same main report section.

If you can't figure this out, reply back with:
The Name property of your subreport control:
The Name property of your total text box located on your subreport:


--
Duane Hookom
Microsoft Access MVP

Boy you ain't kidding!

Is that the name on the other tab if I right click the subforom while in the
main form design mode and bring up properties? If so it is "Invoice Expense
Subreport" for the expense subreport. The source object is "Report.Invoice
Expense Subreport". Neither works in the expression.

Thanks,
Scott B

This stuff isn't easy to understand or explain

A main report can have controls on it. These controls are typical text
boxes, labels, rectangles, etc. One type of control you can add to a report
is a subreport control. The subreport CONTROL has a couple important
properties:

Name: the name of the control which you can leave or edit
Source Object: the name of the subreport from the database window

Typically the Name and Source Object are the same but not always.

When I state "subrptControlName", I am referring to the Name property, not
the Source Object. In these expressions, the Source Object is not
significant. When I want to refer to the actual report contained in the
subreport control I use:
"subrptControlName.Report"
When I want to reference a text box (txtTotal) on the subreport, I use:
=subrptControlName.Report.txtTotal
The above will not display a 0 if the subreport doesn't have any data.
That's why we extend the expression to use the HasData property of the report:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

--
Duane Hookom
Microsoft Access MVP

OK. I am still unclear about subrptControlName. Is that the subreport's name?
Or something on the subreport? I cannot find a listing on the properties of
the subreport or the main report called control name. I am sorry to be so
dense. This ring a very distant bell, but I seem deaf today.

Best,
Scott B

:

You should have the word "Report" in your expressions. "Subreport" is not to
be included unless you have something in your reports named "subreport".

=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

The above on your main report will reference a subreport control named
"subrptControlName". This is the name of the control, not necessarily the
Source Object. "Report" is the same in all expressions. It references the
Report contained in subrptControlName. txtTotal is the name of the text box
on the subreport. This is usually in the subreport's footer section.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

Thanks. I put both of these variations in the subreport total fields. I get
#Name? errors in the subreport total fields and #Error errors in both the
main form subtotal and grand total fields in both instances.

=IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense
Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment
Subreport]. Invoice.PaymentTotal,0)

=IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice
Expense Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice
Payment Subreport]. Invoice.PaymentTotal,0)

I am VERY confused! I really appreciate the help.

Best,
Scott B

:

Since you are actually referencing subreports and not subforms, you should be
able to grab a total or 0 from a subreport with and expression like:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)
I don't know why the MS Template used Reports!MainReportName...
--
Duane Hookom
Microsoft Access MVP


:

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

:

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t




:

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP


:

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly
Billings]
Changed to this when I added the payments subreport:
=sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total
Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments])
***Both Work perfectly!***

Time Subreport Total Billed field. . .
was & is:
[BillableHours]*[BillingRate]
***Works perfectly!***

Problems are in the following fields:
Expenses Subreport Expense Amount field . . .
was:
Forms![Print Invoice]![Total Expenses]
changed to:
iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0)
***Get this error - #Name?***
if changed to:
=sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Print Invoice!Total
Expenses.HasData

Payment Subreport Payment Amount field . . .
was:
Forms![Clients]![Clients Subform]![Total Payments]
changed to:
=iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)
***#Name?***
=sum(iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Clients!Clients Subform!Total

Many thanks,
Scott B

 

[scottyboyb]

Hello,

I'm sorry, but I am bugged eyed over this. And my wife is screaming to get
to something useful. So a little stress.

The names of the subREPORTS are:
Invoice Payments Subreport
Invoice Expense Subreport
The names of the controls on the subreports are:
PaymentTotal
=IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments
Subreport].Report.PaymentTotal,0)
ExpenseTotal
=IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense
Subreport].Report.ExpenseTotal,0)

Still getting #Error. Is there a naming convention that I need to know
about? Like not naming controls the same as fields? Would it help to see
theSQL for the Reports?

Hope this helps you clear this up.
Best,
Scott B

The properties I am referring to are the properties of the subreport control
on the main report. I'm not sure why you use "form" in your replies since I
expect you are dealing only with "report" objects.

Please provide the actual expressions you are attempting to use. I should
also add that your text box on the main report that reference the subreport
should all be in the same main report section.

If you can't figure this out, reply back with:
The Name property of your subreport control:
The Name property of your total text box located on your subreport:


--
Duane Hookom
Microsoft Access MVP

Boy you ain't kidding!

Is that the name on the other tab if I right click the subforom while in the
main form design mode and bring up properties? If so it is "Invoice Expense
Subreport" for the expense subreport. The source object is "Report.Invoice
Expense Subreport". Neither works in the expression.

Thanks,
Scott B

This stuff isn't easy to understand or explain

A main report can have controls on it. These controls are typical text
boxes, labels, rectangles, etc. One type of control you can add to a report
is a subreport control. The subreport CONTROL has a couple important
properties:

Name: the name of the control which you can leave or edit
Source Object: the name of the subreport from the database window

Typically the Name and Source Object are the same but not always.

When I state "subrptControlName", I am referring to the Name property, not
the Source Object. In these expressions, the Source Object is not
significant. When I want to refer to the actual report contained in the
subreport control I use:
"subrptControlName.Report"
When I want to reference a text box (txtTotal) on the subreport, I use:
=subrptControlName.Report.txtTotal
The above will not display a 0 if the subreport doesn't have any data.
That's why we extend the expression to use the HasData property of the report:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

--
Duane Hookom
Microsoft Access MVP


:

OK. I am still unclear about subrptControlName. Is that the subreport's name?
Or something on the subreport? I cannot find a listing on the properties of
the subreport or the main report called control name. I am sorry to be so
dense. This ring a very distant bell, but I seem deaf today.

Best,
Scott B

:

You should have the word "Report" in your expressions. "Subreport" is not to
be included unless you have something in your reports named "subreport".

=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

The above on your main report will reference a subreport control named
"subrptControlName". This is the name of the control, not necessarily the
Source Object. "Report" is the same in all expressions. It references the
Report contained in subrptControlName. txtTotal is the name of the text box
on the subreport. This is usually in the subreport's footer section.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

Thanks. I put both of these variations in the subreport total fields. I get
#Name? errors in the subreport total fields and #Error errors in both the
main form subtotal and grand total fields in both instances.

=IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense
Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment
Subreport]. Invoice.PaymentTotal,0)

=IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice
Expense Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice
Payment Subreport]. Invoice.PaymentTotal,0)

I am VERY confused! I really appreciate the help.

Best,
Scott B

:

Since you are actually referencing subreports and not subforms, you should be
able to grab a total or 0 from a subreport with and expression like:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)
I don't know why the MS Template used Reports!MainReportName...
--
Duane Hookom
Microsoft Access MVP


:

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

:

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t




:

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP


:

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly
Billings]
Changed to this when I added the payments subreport:
=sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total
Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments])
***Both Work perfectly!***

Time Subreport Total Billed field. . .
was & is:
[BillableHours]*[BillingRate]
***Works perfectly!***

Problems are in the following fields:
Expenses Subreport Expense Amount field . . .
was:
Forms![Print Invoice]![Total Expenses]
changed to:
iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0)
***Get this error - #Name?***
if changed to:
=sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Print Invoice!Total
Expenses.HasData

Payment Subreport Payment Amount field . . .
was:
Forms![Clients]![Clients Subform]![Total Payments]
changed to:
=iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)
***#Name?***
=sum(iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Clients!Clients Subform!Total

Many thanks,
Scott B

 

[Duane Hookom]

I asked for:
"The Name property of your subreport control:"
and you gave me the name of your subreport.

I also made the comment:
"I should also add that your text box on the main report that reference
the subreport should all be in the same main report section"
which you didn't acknowledge or respond to.

I may join your screaming wife ;-)

--
Duane Hookom
Microsoft Access MVP

Hello,

I'm sorry, but I am bugged eyed over this. And my wife is screaming to get
to something useful. So a little stress.

The names of the subREPORTS are:
Invoice Payments Subreport
Invoice Expense Subreport
The names of the controls on the subreports are:
PaymentTotal
=IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments
Subreport].Report.PaymentTotal,0)
ExpenseTotal
=IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense
Subreport].Report.ExpenseTotal,0)

Still getting #Error. Is there a naming convention that I need to know
about? Like not naming controls the same as fields? Would it help to see
theSQL for the Reports?

Hope this helps you clear this up.
Best,
Scott B

The properties I am referring to are the properties of the subreport control
on the main report. I'm not sure why you use "form" in your replies since I
expect you are dealing only with "report" objects.

Please provide the actual expressions you are attempting to use. I should
also add that your text box on the main report that reference the subreport
should all be in the same main report section.

If you can't figure this out, reply back with:
The Name property of your subreport control:
The Name property of your total text box located on your subreport:


--
Duane Hookom
Microsoft Access MVP

Boy you ain't kidding!

Is that the name on the other tab if I right click the subforom while in the
main form design mode and bring up properties? If so it is "Invoice Expense
Subreport" for the expense subreport. The source object is "Report.Invoice
Expense Subreport". Neither works in the expression.

Thanks,
Scott B

:

This stuff isn't easy to understand or explain

A main report can have controls on it. These controls are typical text
boxes, labels, rectangles, etc. One type of control you can add to a report
is a subreport control. The subreport CONTROL has a couple important
properties:

Name: the name of the control which you can leave or edit
Source Object: the name of the subreport from the database window

Typically the Name and Source Object are the same but not always.

When I state "subrptControlName", I am referring to the Name property, not
the Source Object. In these expressions, the Source Object is not
significant. When I want to refer to the actual report contained in the
subreport control I use:
"subrptControlName.Report"
When I want to reference a text box (txtTotal) on the subreport, I use:
=subrptControlName.Report.txtTotal
The above will not display a 0 if the subreport doesn't have any data.
That's why we extend the expression to use the HasData property of the report:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

--
Duane Hookom
Microsoft Access MVP


:

OK. I am still unclear about subrptControlName. Is that the subreport's name?
Or something on the subreport? I cannot find a listing on the properties of
the subreport or the main report called control name. I am sorry to be so
dense. This ring a very distant bell, but I seem deaf today.

Best,
Scott B

:

You should have the word "Report" in your expressions. "Subreport" is not to
be included unless you have something in your reports named "subreport".

=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

The above on your main report will reference a subreport control named
"subrptControlName". This is the name of the control, not necessarily the
Source Object. "Report" is the same in all expressions. It references the
Report contained in subrptControlName. txtTotal is the name of the text box
on the subreport. This is usually in the subreport's footer section.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

Thanks. I put both of these variations in the subreport total fields. I get
#Name? errors in the subreport total fields and #Error errors in both the
main form subtotal and grand total fields in both instances.

=IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense
Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment
Subreport]. Invoice.PaymentTotal,0)

=IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice
Expense Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice
Payment Subreport]. Invoice.PaymentTotal,0)

I am VERY confused! I really appreciate the help.

Best,
Scott B

:

Since you are actually referencing subreports and not subforms, you should be
able to grab a total or 0 from a subreport with and expression like:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)
I don't know why the MS Template used Reports!MainReportName...
--
Duane Hookom
Microsoft Access MVP


:

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

:

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t




:

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP


:

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly
Billings]
Changed to this when I added the payments subreport:
=sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total
Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments])
***Both Work perfectly!***

Time Subreport Total Billed field. . .
was & is:
[BillableHours]*[BillingRate]
***Works perfectly!***

Problems are in the following fields:
Expenses Subreport Expense Amount field . . .
was:
Forms![Print Invoice]![Total Expenses]
changed to:
iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0)
***Get this error - #Name?***
if changed to:
=sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Print Invoice!Total
Expenses.HasData

Payment Subreport Payment Amount field . . .
was:
Forms![Clients]![Clients Subform]![Total Payments]
changed to:
=iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)
***#Name?***
=sum(iif(Forms![Clients]![Clients Subform]![Total
Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Clients!Clients Subform!Total

Many thanks,
Scott B

 

[scottyboyb]

The name I gave you is the name of the control. If it isn't, I do not know
where to get it from. When I click on the subreport in the main report, and
open the properties for the subreport, the name line of the Other Tab says
what I wrote. It appears that they are one in the same. And the subreports
are all in the same detail section of the main report.

I asked for:
"The Name property of your subreport control:"
and you gave me the name of your subreport.

I also made the comment:
"I should also add that your text box on the main report that reference
the subreport should all be in the same main report section"
which you didn't acknowledge or respond to.

I may join your screaming wife ;-)

--
Duane Hookom
Microsoft Access MVP

Hello,

I'm sorry, but I am bugged eyed over this. And my wife is screaming to get
to something useful. So a little stress.

The names of the subREPORTS are:
Invoice Payments Subreport
Invoice Expense Subreport
The names of the controls on the subreports are:
PaymentTotal
=IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments
Subreport].Report.PaymentTotal,0)
ExpenseTotal
=IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense
Subreport].Report.ExpenseTotal,0)

Still getting #Error. Is there a naming convention that I need to know
about? Like not naming controls the same as fields? Would it help to see
theSQL for the Reports?

Hope this helps you clear this up.
Best,
Scott B

The properties I am referring to are the properties of the subreport control
on the main report. I'm not sure why you use "form" in your replies since I
expect you are dealing only with "report" objects.

Please provide the actual expressions you are attempting to use. I should
also add that your text box on the main report that reference the subreport
should all be in the same main report section.

If you can't figure this out, reply back with:
The Name property of your subreport control:
The Name property of your total text box located on your subreport:


--
Duane Hookom
Microsoft Access MVP


:

Boy you ain't kidding!

Is that the name on the other tab if I right click the subforom while in the
main form design mode and bring up properties? If so it is "Invoice Expense
Subreport" for the expense subreport. The source object is "Report.Invoice
Expense Subreport". Neither works in the expression.

Thanks,
Scott B

:

This stuff isn't easy to understand or explain

A main report can have controls on it. These controls are typical text
boxes, labels, rectangles, etc. One type of control you can add to a report
is a subreport control. The subreport CONTROL has a couple important
properties:

Name: the name of the control which you can leave or edit
Source Object: the name of the subreport from the database window

Typically the Name and Source Object are the same but not always.

When I state "subrptControlName", I am referring to the Name property, not
the Source Object. In these expressions, the Source Object is not
significant. When I want to refer to the actual report contained in the
subreport control I use:
"subrptControlName.Report"
When I want to reference a text box (txtTotal) on the subreport, I use:
=subrptControlName.Report.txtTotal
The above will not display a 0 if the subreport doesn't have any data.
That's why we extend the expression to use the HasData property of the report:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

--
Duane Hookom
Microsoft Access MVP


:

OK. I am still unclear about subrptControlName. Is that the subreport's name?
Or something on the subreport? I cannot find a listing on the properties of
the subreport or the main report called control name. I am sorry to be so
dense. This ring a very distant bell, but I seem deaf today.

Best,
Scott B

:

You should have the word "Report" in your expressions. "Subreport" is not to
be included unless you have something in your reports named "subreport".

=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

The above on your main report will reference a subreport control named
"subrptControlName". This is the name of the control, not necessarily the
Source Object. "Report" is the same in all expressions. It references the
Report contained in subrptControlName. txtTotal is the name of the text box
on the subreport. This is usually in the subreport's footer section.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

Thanks. I put both of these variations in the subreport total fields. I get
#Name? errors in the subreport total fields and #Error errors in both the
main form subtotal and grand total fields in both instances.

=IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense
Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment
Subreport]. Invoice.PaymentTotal,0)

=IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice
Expense Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice
Payment Subreport]. Invoice.PaymentTotal,0)

I am VERY confused! I really appreciate the help.

Best,
Scott B

:

Since you are actually referencing subreports and not subforms, you should be
able to grab a total or 0 from a subreport with and expression like:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)
I don't know why the MS Template used Reports!MainReportName...
--
Duane Hookom
Microsoft Access MVP


:

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

:

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t




:

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP


:

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly
Billings]
Changed to this when I added the payments subreport:
=sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total
Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments])
***Both Work perfectly!***

Time Subreport Total Billed field. . .
was & is:
[BillableHours]*[BillingRate]
***Works perfectly!***

Problems are in the following fields:
Expenses Subreport Expense Amount field . . .
was:
Forms![Print Invoice]![Total Expenses]
changed to:
iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0)
***Get this error - #Name?***
if changed to:
=sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print
Invoice]![Total Expenses],0))
***$0.00 even when there is data*** and I get an "enter parameter value"
popup window before the form loads for Forms!Print Invoice!Total
Expenses.HasData

 

[Duane Hookom]

Sorry, I am a stickler for details. You stated:
"And the subreports are all in the same detail section of the main report."

Is the text box that references the subreports in the detail section also?

Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport?

What do you get if you simply use a control source of:
=[Invoice Payments Subreport].Report.PaymentTotal

--
Duane Hookom
Microsoft Access MVP

The name I gave you is the name of the control. If it isn't, I do not know
where to get it from. When I click on the subreport in the main report, and
open the properties for the subreport, the name line of the Other Tab says
what I wrote. It appears that they are one in the same. And the subreports
are all in the same detail section of the main report.

I asked for:
"The Name property of your subreport control:"
and you gave me the name of your subreport.

I also made the comment:
"I should also add that your text box on the main report that reference
the subreport should all be in the same main report section"
which you didn't acknowledge or respond to.

I may join your screaming wife ;-)

--
Duane Hookom
Microsoft Access MVP

Hello,

I'm sorry, but I am bugged eyed over this. And my wife is screaming to get
to something useful. So a little stress.

The names of the subREPORTS are:
Invoice Payments Subreport
Invoice Expense Subreport
The names of the controls on the subreports are:
PaymentTotal
=IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments
Subreport].Report.PaymentTotal,0)
ExpenseTotal
=IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense
Subreport].Report.ExpenseTotal,0)

Still getting #Error. Is there a naming convention that I need to know
about? Like not naming controls the same as fields? Would it help to see
theSQL for the Reports?

Hope this helps you clear this up.
Best,
Scott B


:

The properties I am referring to are the properties of the subreport control
on the main report. I'm not sure why you use "form" in your replies since I
expect you are dealing only with "report" objects.

Please provide the actual expressions you are attempting to use. I should
also add that your text box on the main report that reference the subreport
should all be in the same main report section.

If you can't figure this out, reply back with:
The Name property of your subreport control:
The Name property of your total text box located on your subreport:


--
Duane Hookom
Microsoft Access MVP


:

Boy you ain't kidding!

Is that the name on the other tab if I right click the subforom while in the
main form design mode and bring up properties? If so it is "Invoice Expense
Subreport" for the expense subreport. The source object is "Report.Invoice
Expense Subreport". Neither works in the expression.

Thanks,
Scott B

:

This stuff isn't easy to understand or explain

A main report can have controls on it. These controls are typical text
boxes, labels, rectangles, etc. One type of control you can add to a report
is a subreport control. The subreport CONTROL has a couple important
properties:

Name: the name of the control which you can leave or edit
Source Object: the name of the subreport from the database window

Typically the Name and Source Object are the same but not always.

When I state "subrptControlName", I am referring to the Name property, not
the Source Object. In these expressions, the Source Object is not
significant. When I want to refer to the actual report contained in the
subreport control I use:
"subrptControlName.Report"
When I want to reference a text box (txtTotal) on the subreport, I use:
=subrptControlName.Report.txtTotal
The above will not display a 0 if the subreport doesn't have any data.
That's why we extend the expression to use the HasData property of the report:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

--
Duane Hookom
Microsoft Access MVP


:

OK. I am still unclear about subrptControlName. Is that the subreport's name?
Or something on the subreport? I cannot find a listing on the properties of
the subreport or the main report called control name. I am sorry to be so
dense. This ring a very distant bell, but I seem deaf today.

Best,
Scott B

:

You should have the word "Report" in your expressions. "Subreport" is not to
be included unless you have something in your reports named "subreport".

=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

The above on your main report will reference a subreport control named
"subrptControlName". This is the name of the control, not necessarily the
Source Object. "Report" is the same in all expressions. It references the
Report contained in subrptControlName. txtTotal is the name of the text box
on the subreport. This is usually in the subreport's footer section.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

Thanks. I put both of these variations in the subreport total fields. I get
#Name? errors in the subreport total fields and #Error errors in both the
main form subtotal and grand total fields in both instances.

=IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense
Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment
Subreport]. Invoice.PaymentTotal,0)

=IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice
Expense Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice
Payment Subreport]. Invoice.PaymentTotal,0)

I am VERY confused! I really appreciate the help.

Best,
Scott B

:

Since you are actually referencing subreports and not subforms, you should be
able to grab a total or 0 from a subreport with and expression like:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)
I don't know why the MS Template used Reports!MainReportName...
--
Duane Hookom
Microsoft Access MVP


:

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

:

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t




:

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP


:

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly
Billings]
Changed to this when I added the payments subreport:
=sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total
Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments])
***Both Work perfectly!***

Time Subreport Total Billed field. . .
was & is:
[BillableHours]*[BillingRate]
***Works perfectly!***

Problems are in the following fields:
Expenses Subreport Expense Amount field . . .
was:
Forms![Print Invoice]![Total Expenses]
changed to:
iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print

 

[scottyboyb]

Yes. All in Detail Section.
The order is as follows:
Hours worked subrpt
Expenses subrpt
subtotal control (worked + expenses)
Payment subrpt
grand total control

Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport?

No, they are in a SORT footer. Sholuld I try moving them?

What do you get if you simply use a control source of:
=[Invoice Payments Subreport].Report.PaymentTotal

#Name?

Best,
Scott

Sorry, I am a stickler for details. You stated:
"And the subreports are all in the same detail section of the main report."

Is the text box that references the subreports in the detail section also?

Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport?

What do you get if you simply use a control source of:
=[Invoice Payments Subreport].Report.PaymentTotal

--
Duane Hookom
Microsoft Access MVP

The name I gave you is the name of the control. If it isn't, I do not know
where to get it from. When I click on the subreport in the main report, and
open the properties for the subreport, the name line of the Other Tab says
what I wrote. It appears that they are one in the same. And the subreports
are all in the same detail section of the main report.

I asked for:
"The Name property of your subreport control:"
and you gave me the name of your subreport.

I also made the comment:
"I should also add that your text box on the main report that reference
the subreport should all be in the same main report section"
which you didn't acknowledge or respond to.

I may join your screaming wife ;-)

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I'm sorry, but I am bugged eyed over this. And my wife is screaming to get
to something useful. So a little stress.

The names of the subREPORTS are:
Invoice Payments Subreport
Invoice Expense Subreport
The names of the controls on the subreports are:
PaymentTotal
=IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments
Subreport].Report.PaymentTotal,0)
ExpenseTotal
=IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense
Subreport].Report.ExpenseTotal,0)

Still getting #Error. Is there a naming convention that I need to know
about? Like not naming controls the same as fields? Would it help to see
theSQL for the Reports?

Hope this helps you clear this up.
Best,
Scott B


:

The properties I am referring to are the properties of the subreport control
on the main report. I'm not sure why you use "form" in your replies since I
expect you are dealing only with "report" objects.

Please provide the actual expressions you are attempting to use. I should
also add that your text box on the main report that reference the subreport
should all be in the same main report section.

If you can't figure this out, reply back with:
The Name property of your subreport control:
The Name property of your total text box located on your subreport:


--
Duane Hookom
Microsoft Access MVP


:

Boy you ain't kidding!

Is that the name on the other tab if I right click the subforom while in the
main form design mode and bring up properties? If so it is "Invoice Expense
Subreport" for the expense subreport. The source object is "Report.Invoice
Expense Subreport". Neither works in the expression.

Thanks,
Scott B

:

This stuff isn't easy to understand or explain

A main report can have controls on it. These controls are typical text
boxes, labels, rectangles, etc. One type of control you can add to a report
is a subreport control. The subreport CONTROL has a couple important
properties:

Name: the name of the control which you can leave or edit
Source Object: the name of the subreport from the database window

Typically the Name and Source Object are the same but not always.

When I state "subrptControlName", I am referring to the Name property, not
the Source Object. In these expressions, the Source Object is not
significant. When I want to refer to the actual report contained in the
subreport control I use:
"subrptControlName.Report"
When I want to reference a text box (txtTotal) on the subreport, I use:
=subrptControlName.Report.txtTotal
The above will not display a 0 if the subreport doesn't have any data.
That's why we extend the expression to use the HasData property of the report:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

--
Duane Hookom
Microsoft Access MVP


:

OK. I am still unclear about subrptControlName. Is that the subreport's name?
Or something on the subreport? I cannot find a listing on the properties of
the subreport or the main report called control name. I am sorry to be so
dense. This ring a very distant bell, but I seem deaf today.

Best,
Scott B

:

You should have the word "Report" in your expressions. "Subreport" is not to
be included unless you have something in your reports named "subreport".

=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

The above on your main report will reference a subreport control named
"subrptControlName". This is the name of the control, not necessarily the
Source Object. "Report" is the same in all expressions. It references the
Report contained in subrptControlName. txtTotal is the name of the text box
on the subreport. This is usually in the subreport's footer section.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

Thanks. I put both of these variations in the subreport total fields. I get
#Name? errors in the subreport total fields and #Error errors in both the
main form subtotal and grand total fields in both instances.

=IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense
Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment
Subreport]. Invoice.PaymentTotal,0)

=IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice
Expense Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice
Payment Subreport]. Invoice.PaymentTotal,0)

I am VERY confused! I really appreciate the help.

Best,
Scott B

:

Since you are actually referencing subreports and not subforms, you should be
able to grab a total or 0 from a subreport with and expression like:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)
I don't know why the MS Template used Reports!MainReportName...
--
Duane Hookom
Microsoft Access MVP


:

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

:

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t




:

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP


:

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and
amount due.

The second form, "Print Invoice" form is opened from the clients form and
provides basic project info plus it asks for an invoice description and start
and end date parameters. The subform contains total hours, total hourly
billing, total expenses and amount due. That data is summarozed on the print
invoice main forma nd that is where the fieldnames come from for the report.
A button on the second form opens a preview of the invoice report.

I have checked and rechecked the field names and I cannot find an error. I
should also tell you that I am getting the right numbers on the totals that
do work.

Please let me know if I need to give you more info.

Here is what I am getting on the Report.

Invoice report has 3 subreports: time, expense and payments.
Invoice Report Total Due field . . .
Was:
Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly

 

[Duane Hookom]

"No, they are in a SORT footer. Should I try moving them?" Is the SORT footer
based on the same field as the Link Master/Child? Try move or copy it to the
Report Footer.
--
Duane Hookom
Microsoft Access MVP

Yes. All in Detail Section.
The order is as follows:
Hours worked subrpt
Expenses subrpt
subtotal control (worked + expenses)
Payment subrpt
grand total control

Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport?

No, they are in a SORT footer. Sholuld I try moving them?

What do you get if you simply use a control source of:
=[Invoice Payments Subreport].Report.PaymentTotal

#Name?

Best,
Scott

Sorry, I am a stickler for details. You stated:
"And the subreports are all in the same detail section of the main report."

Is the text box that references the subreports in the detail section also?

Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport?

What do you get if you simply use a control source of:
=[Invoice Payments Subreport].Report.PaymentTotal

--
Duane Hookom
Microsoft Access MVP

The name I gave you is the name of the control. If it isn't, I do not know
where to get it from. When I click on the subreport in the main report, and
open the properties for the subreport, the name line of the Other Tab says
what I wrote. It appears that they are one in the same. And the subreports
are all in the same detail section of the main report.

:

I asked for:
"The Name property of your subreport control:"
and you gave me the name of your subreport.

I also made the comment:
"I should also add that your text box on the main report that reference
the subreport should all be in the same main report section"
which you didn't acknowledge or respond to.

I may join your screaming wife ;-)

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I'm sorry, but I am bugged eyed over this. And my wife is screaming to get
to something useful. So a little stress.

The names of the subREPORTS are:
Invoice Payments Subreport
Invoice Expense Subreport
The names of the controls on the subreports are:
PaymentTotal
=IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments
Subreport].Report.PaymentTotal,0)
ExpenseTotal
=IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense
Subreport].Report.ExpenseTotal,0)

Still getting #Error. Is there a naming convention that I need to know
about? Like not naming controls the same as fields? Would it help to see
theSQL for the Reports?

Hope this helps you clear this up.
Best,
Scott B


:

The properties I am referring to are the properties of the subreport control
on the main report. I'm not sure why you use "form" in your replies since I
expect you are dealing only with "report" objects.

Please provide the actual expressions you are attempting to use. I should
also add that your text box on the main report that reference the subreport
should all be in the same main report section.

If you can't figure this out, reply back with:
The Name property of your subreport control:
The Name property of your total text box located on your subreport:


--
Duane Hookom
Microsoft Access MVP


:

Boy you ain't kidding!

Is that the name on the other tab if I right click the subforom while in the
main form design mode and bring up properties? If so it is "Invoice Expense
Subreport" for the expense subreport. The source object is "Report.Invoice
Expense Subreport". Neither works in the expression.

Thanks,
Scott B

:

This stuff isn't easy to understand or explain

A main report can have controls on it. These controls are typical text
boxes, labels, rectangles, etc. One type of control you can add to a report
is a subreport control. The subreport CONTROL has a couple important
properties:

Name: the name of the control which you can leave or edit
Source Object: the name of the subreport from the database window

Typically the Name and Source Object are the same but not always.

When I state "subrptControlName", I am referring to the Name property, not
the Source Object. In these expressions, the Source Object is not
significant. When I want to refer to the actual report contained in the
subreport control I use:
"subrptControlName.Report"
When I want to reference a text box (txtTotal) on the subreport, I use:
=subrptControlName.Report.txtTotal
The above will not display a 0 if the subreport doesn't have any data.
That's why we extend the expression to use the HasData property of the report:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

--
Duane Hookom
Microsoft Access MVP


:

OK. I am still unclear about subrptControlName. Is that the subreport's name?
Or something on the subreport? I cannot find a listing on the properties of
the subreport or the main report called control name. I am sorry to be so
dense. This ring a very distant bell, but I seem deaf today.

Best,
Scott B

:

You should have the word "Report" in your expressions. "Subreport" is not to
be included unless you have something in your reports named "subreport".

=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

The above on your main report will reference a subreport control named
"subrptControlName". This is the name of the control, not necessarily the
Source Object. "Report" is the same in all expressions. It references the
Report contained in subrptControlName. txtTotal is the name of the text box
on the subreport. This is usually in the subreport's footer section.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

Thanks. I put both of these variations in the subreport total fields. I get
#Name? errors in the subreport total fields and #Error errors in both the
main form subtotal and grand total fields in both instances.

=IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense
Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment
Subreport]. Invoice.PaymentTotal,0)

=IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice
Expense Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice
Payment Subreport]. Invoice.PaymentTotal,0)

I am VERY confused! I really appreciate the help.

Best,
Scott B

:

Since you are actually referencing subreports and not subforms, you should be
able to grab a total or 0 from a subreport with and expression like:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)
I don't know why the MS Template used Reports!MainReportName...
--
Duane Hookom
Microsoft Access MVP


:

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

:

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t




:

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--
Duane Hookom
Microsoft Access MVP


:

I have an Invoice Report. It draws it's data from two forms. One is the
"Clients" form with the "Clients Subform". The Clients form does as it's name
implies, providing client data. The subform provides project info like
project name, labor hours worked, labor dollars owed, payments made and

 

[scottyboyb]

Good morning.

"No, they are in a SORT footer. Should I try moving them?" Is the SORT footer
based on the same field as the Link Master/Child? Try move or copy it to the
Report Footer.

The SORT footer is ProjectID and so the Link Master and Child fields. I did
try moving then qand it had no efect.

Is this going to be a record for number of posts and difficulty? I cannot
think of a time I have had this much trouble, even when I was just starting
and did not know what a link master field was.

I cannot thank you enough fir your help and patience. Onr thing Ithought of
last night. Is there another way to do this? Are we trying to make a square
peg fit in a round hole?

Best,
Scott

"No, they are in a SORT footer. Should I try moving them?" Is the SORT footer
based on the same field as the Link Master/Child? Try move or copy it to the
Report Footer.
--
Duane Hookom
Microsoft Access MVP

Yes. All in Detail Section.
The order is as follows:
Hours worked subrpt
Expenses subrpt
subtotal control (worked + expenses)
Payment subrpt
grand total control

Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport?

No, they are in a SORT footer. Sholuld I try moving them?

What do you get if you simply use a control source of:
=[Invoice Payments Subreport].Report.PaymentTotal

#Name?

Best,
Scott

Sorry, I am a stickler for details. You stated:
"And the subreports are all in the same detail section of the main report."

Is the text box that references the subreports in the detail section also?

Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport?

What do you get if you simply use a control source of:
=[Invoice Payments Subreport].Report.PaymentTotal

--
Duane Hookom
Microsoft Access MVP


:

The name I gave you is the name of the control. If it isn't, I do not know
where to get it from. When I click on the subreport in the main report, and
open the properties for the subreport, the name line of the Other Tab says
what I wrote. It appears that they are one in the same. And the subreports
are all in the same detail section of the main report.

:

I asked for:
"The Name property of your subreport control:"
and you gave me the name of your subreport.

I also made the comment:
"I should also add that your text box on the main report that reference
the subreport should all be in the same main report section"
which you didn't acknowledge or respond to.

I may join your screaming wife ;-)

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I'm sorry, but I am bugged eyed over this. And my wife is screaming to get
to something useful. So a little stress.

The names of the subREPORTS are:
Invoice Payments Subreport
Invoice Expense Subreport
The names of the controls on the subreports are:
PaymentTotal
=IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments
Subreport].Report.PaymentTotal,0)
ExpenseTotal
=IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense
Subreport].Report.ExpenseTotal,0)

Still getting #Error. Is there a naming convention that I need to know
about? Like not naming controls the same as fields? Would it help to see
theSQL for the Reports?

Hope this helps you clear this up.
Best,
Scott B


:

The properties I am referring to are the properties of the subreport control
on the main report. I'm not sure why you use "form" in your replies since I
expect you are dealing only with "report" objects.

Please provide the actual expressions you are attempting to use. I should
also add that your text box on the main report that reference the subreport
should all be in the same main report section.

If you can't figure this out, reply back with:
The Name property of your subreport control:
The Name property of your total text box located on your subreport:


--
Duane Hookom
Microsoft Access MVP


:

Boy you ain't kidding!

Is that the name on the other tab if I right click the subforom while in the
main form design mode and bring up properties? If so it is "Invoice Expense
Subreport" for the expense subreport. The source object is "Report.Invoice
Expense Subreport". Neither works in the expression.

Thanks,
Scott B

:

This stuff isn't easy to understand or explain

A main report can have controls on it. These controls are typical text
boxes, labels, rectangles, etc. One type of control you can add to a report
is a subreport control. The subreport CONTROL has a couple important
properties:

Name: the name of the control which you can leave or edit
Source Object: the name of the subreport from the database window

Typically the Name and Source Object are the same but not always.

When I state "subrptControlName", I am referring to the Name property, not
the Source Object. In these expressions, the Source Object is not
significant. When I want to refer to the actual report contained in the
subreport control I use:
"subrptControlName.Report"
When I want to reference a text box (txtTotal) on the subreport, I use:
=subrptControlName.Report.txtTotal
The above will not display a 0 if the subreport doesn't have any data.
That's why we extend the expression to use the HasData property of the report:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

--
Duane Hookom
Microsoft Access MVP


:

OK. I am still unclear about subrptControlName. Is that the subreport's name?
Or something on the subreport? I cannot find a listing on the properties of
the subreport or the main report called control name. I am sorry to be so
dense. This ring a very distant bell, but I seem deaf today.

Best,
Scott B

:

You should have the word "Report" in your expressions. "Subreport" is not to
be included unless you have something in your reports named "subreport".

=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

The above on your main report will reference a subreport control named
"subrptControlName". This is the name of the control, not necessarily the
Source Object. "Report" is the same in all expressions. It references the
Report contained in subrptControlName. txtTotal is the name of the text box
on the subreport. This is usually in the subreport's footer section.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

Thanks. I put both of these variations in the subreport total fields. I get
#Name? errors in the subreport total fields and #Error errors in both the
main form subtotal and grand total fields in both instances.

=IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense
Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment
Subreport]. Invoice.PaymentTotal,0)

=IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice
Expense Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice
Payment Subreport]. Invoice.PaymentTotal,0)

I am VERY confused! I really appreciate the help.

Best,
Scott B

:

Since you are actually referencing subreports and not subforms, you should be
able to grab a total or 0 from a subreport with and expression like:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)
I don't know why the MS Template used Reports!MainReportName...
--
Duane Hookom
Microsoft Access MVP


:

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

:

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total
Subreport 2 gives expenses total
Add them and get:
Main Report Subtotal
Subreport 3 = payments
Subtract subreport 3 total from Main Report subtotal to get grand total.

All works fine as long as all subreports have data. t




:

Forms don't "have" data. They generally pull data from tables. Why are iryou
not just pulling data from tables for your report. Forms don't have a
"HasData" property like reports.

--

 

[Duane Hookom]

The only other thing I can think of with the #Name error is the Name
property of the text box displaying #Name, can't be the name of a field in
the report's record source. I would rename the text box to txtNetTotal or
similar.

Good suggestion on the alternative method. Generally, anything that can be
totaled in a subreport can be totaled in a group by/totals query. I expect
you could create a totals query by ProjectID similar to the record source of
your subreport and join it into the record source of your main report. Join
the ProjectID fields and drop the totaled/summed field into the main report's
fields.
--
Duane Hookom
Microsoft Access MVP

Good morning.

"No, they are in a SORT footer. Should I try moving them?" Is the SORT footer
based on the same field as the Link Master/Child? Try move or copy it to the
Report Footer.

The SORT footer is ProjectID and so the Link Master and Child fields. I did
try moving then qand it had no efect.

Is this going to be a record for number of posts and difficulty? I cannot
think of a time I have had this much trouble, even when I was just starting
and did not know what a link master field was.

I cannot thank you enough fir your help and patience. Onr thing Ithought of
last night. Is there another way to do this? Are we trying to make a square
peg fit in a round hole?

Best,
Scott

"No, they are in a SORT footer. Should I try moving them?" Is the SORT footer
based on the same field as the Link Master/Child? Try move or copy it to the
Report Footer.
--
Duane Hookom
Microsoft Access MVP

Yes. All in Detail Section.
The order is as follows:
Hours worked subrpt
Expenses subrpt
subtotal control (worked + expenses)
Payment subrpt
grand total control

Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport?
No, they are in a SORT footer. Sholuld I try moving them?

What do you get if you simply use a control source of:
=[Invoice Payments Subreport].Report.PaymentTotal
#Name?

Best,
Scott


:

Sorry, I am a stickler for details. You stated:
"And the subreports are all in the same detail section of the main report."

Is the text box that references the subreports in the detail section also?

Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport?

What do you get if you simply use a control source of:
=[Invoice Payments Subreport].Report.PaymentTotal

--
Duane Hookom
Microsoft Access MVP


:

The name I gave you is the name of the control. If it isn't, I do not know
where to get it from. When I click on the subreport in the main report, and
open the properties for the subreport, the name line of the Other Tab says
what I wrote. It appears that they are one in the same. And the subreports
are all in the same detail section of the main report.

:

I asked for:
"The Name property of your subreport control:"
and you gave me the name of your subreport.

I also made the comment:
"I should also add that your text box on the main report that reference
the subreport should all be in the same main report section"
which you didn't acknowledge or respond to.

I may join your screaming wife ;-)

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I'm sorry, but I am bugged eyed over this. And my wife is screaming to get
to something useful. So a little stress.

The names of the subREPORTS are:
Invoice Payments Subreport
Invoice Expense Subreport
The names of the controls on the subreports are:
PaymentTotal
=IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments
Subreport].Report.PaymentTotal,0)
ExpenseTotal
=IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense
Subreport].Report.ExpenseTotal,0)

Still getting #Error. Is there a naming convention that I need to know
about? Like not naming controls the same as fields? Would it help to see
theSQL for the Reports?

Hope this helps you clear this up.
Best,
Scott B


:

The properties I am referring to are the properties of the subreport control
on the main report. I'm not sure why you use "form" in your replies since I
expect you are dealing only with "report" objects.

Please provide the actual expressions you are attempting to use. I should
also add that your text box on the main report that reference the subreport
should all be in the same main report section.

If you can't figure this out, reply back with:
The Name property of your subreport control:
The Name property of your total text box located on your subreport:


--
Duane Hookom
Microsoft Access MVP


:

Boy you ain't kidding!

Is that the name on the other tab if I right click the subforom while in the
main form design mode and bring up properties? If so it is "Invoice Expense
Subreport" for the expense subreport. The source object is "Report.Invoice
Expense Subreport". Neither works in the expression.

Thanks,
Scott B

:

This stuff isn't easy to understand or explain

A main report can have controls on it. These controls are typical text
boxes, labels, rectangles, etc. One type of control you can add to a report
is a subreport control. The subreport CONTROL has a couple important
properties:

Name: the name of the control which you can leave or edit
Source Object: the name of the subreport from the database window

Typically the Name and Source Object are the same but not always.

When I state "subrptControlName", I am referring to the Name property, not
the Source Object. In these expressions, the Source Object is not
significant. When I want to refer to the actual report contained in the
subreport control I use:
"subrptControlName.Report"
When I want to reference a text box (txtTotal) on the subreport, I use:
=subrptControlName.Report.txtTotal
The above will not display a 0 if the subreport doesn't have any data.
That's why we extend the expression to use the HasData property of the report:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

--
Duane Hookom
Microsoft Access MVP


:

OK. I am still unclear about subrptControlName. Is that the subreport's name?
Or something on the subreport? I cannot find a listing on the properties of
the subreport or the main report called control name. I am sorry to be so
dense. This ring a very distant bell, but I seem deaf today.

Best,
Scott B

:

You should have the word "Report" in your expressions. "Subreport" is not to
be included unless you have something in your reports named "subreport".

=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)

The above on your main report will reference a subreport control named
"subrptControlName". This is the name of the control, not necessarily the
Source Object. "Report" is the same in all expressions. It references the
Report contained in subrptControlName. txtTotal is the name of the text box
on the subreport. This is usually in the subreport's footer section.

--
Duane Hookom
Microsoft Access MVP


:

Duane,

Thanks. I put both of these variations in the subreport total fields. I get
#Name? errors in the subreport total fields and #Error errors in both the
main form subtotal and grand total fields in both instances.

=IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense
Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment
Subreport]. Invoice.PaymentTotal,0)

=IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice
Expense Subreport]. Invoice.ExpenseTotal,0)
=IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice
Payment Subreport]. Invoice.PaymentTotal,0)

I am VERY confused! I really appreciate the help.

Best,
Scott B

:

Since you are actually referencing subreports and not subforms, you should be
able to grab a total or 0 from a subreport with and expression like:
=IIf(subrptControlName.Report.HasData,subrptControlName.Report.txtTotal,0)
I don't know why the MS Template used Reports!MainReportName...
--
Duane Hookom
Microsoft Access MVP


:

Duane,

Sorry my finger slipped and I accidentally sent this incompete. Here is the
entire post.

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first
two provide a subtotal for the main report. Then I take the total from the
third subreport and subract it from the main report subtotal and get a grand
total.
Subreport 1 gives work hours total: =Sum([WorkAmount])
Subreport 2 gives expenses total: =Sum([ExpenseAmount])
Add them and get
Main Report Subtotal : =([Reports]![Invoice]![invoice Time
Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense
Subreport]!ExpenseTotal)
Subreport 3 = payments: =Sum([PaymentAmount])
Subtract subreport 3 total from Main Report subtotal to get grand total:
=([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments
Subreport]![PaymentTotal])

All works fine as long as all subreports have data. If any of the above
subreport fields do not have data I get a #Error in the subtotal field and
grand total fields. I think I need a way to test for null values in the
subreport total fields. I have tried:
=Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0))
That gives me a parameter request.
=Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount]))
gives me #Error
=Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount]))
gives me #Error
Plus several others that I have lost track of.

I am trying to be succinct and still be complete. I appologize for the length.


Any thoughts on this?

Best,
Scott B

:

Thank you for your reply. I got this database originally from a Microsoft
template and that is how they wrote it. I took your advice and I changed the
reports to get their totals data from the fields of the report which come
from underlying SQL queries.

So, I as said I have 3 subreports that each have a subtotal field. The first