Subreport not working

  • Thread starter Thread starter Jesse Aviles
  • Start date Start date
J

Jesse Aviles

I have an invoice aging report with a payments received
subreport. Both
reports are based on two different queries. My main
report shows the aging
for each company, which it does nicely and trouble free.
However my
subreport does not show payments received from the
company. They are setup
so that each company will see a list of the invoices we
have sent along with
how far behind they are on their payments.

Report Header
Company Header
Detail
Company Footer
Subreport
Page Footer
Report Footer

The subreport is linked to the main report by the
InvoiceNo. I wanted to
show partial payments we have received for the various
invoices and on the
Grand Total for the company show the amount owed. The
current setup I have
is not working. Payments do not show on the report but
if I run the query I
can see the payments received. If I move the subreport
to the detail
section of the report, I can see partial payments for the
invoices we have
sent but then there will be a huge space between every
invoice listed (see
the accompanying snapshots). Another thing that is not
doing correctly is
the substraction of payments from the amount owed
(=IIf(TotalPaymentsReceivedSubreport.Report.HasData,(Nz
([TotalOutstandingInv
oices])-Nz(TotalPaymentsReceivedSubreport.Report!
SumOfTotalPayment)),[TotalO
utstandingInvoices])). It will always show the invoiced
amount and when I
run the report to show all the companies it will only
show the invoiced
amount of the last company. This is the SQL of the main
report:

SELECT InvoiceDesc.InvoiceNo, InvoiceDesc.ClientID,
InvoiceDesc.Company,
InvoiceDesc.InvoiceDate, InvoiceDesc.PurchaseOrderNumber,
Sum(InvDetTotPrice.LineTotal) AS SumOfLineTotal,
Invoices.Paid,
IIf(Date()-InvoiceDesc!InvoiceDate<31,Sum([LineTotal]))
AS [Current],
IIf(Date()-InvoiceDesc!InvoiceDate>30 And
Date()-InvoiceDesc!InvoiceDate<61,Sum([LineTotal])) AS
[31-60],
IIf(Date()-InvoiceDesc!InvoiceDate>60 And
Date()-InvoiceDesc!InvoiceDate<91,Sum([LineTotal])) AS
[61-90],
IIf(Date()-InvoiceDesc!InvoiceDate>90,Sum([LineTotal]))
AS [Over 90]
FROM (InvoiceDesc INNER JOIN Invoices ON
InvoiceDesc.InvoiceNo =
Invoices.InvoiceNo) INNER JOIN InvDetTotPrice ON
Invoices.InvoiceNo =
InvDetTotPrice.InvoiceNo
GROUP BY InvoiceDesc.InvoiceNo, InvoiceDesc.ClientID,
InvoiceDesc.Company,
InvoiceDesc.InvoiceDate, InvoiceDesc.PurchaseOrderNumber,
Invoices.Paid
HAVING (((Invoices.Paid)=False))
ORDER BY InvoiceDesc.InvoiceNo;

and this is the SQL of the subreport:
SELECT TotalPaymentsReceived.CheckNo,
TotalPaymentsReceived.CheckAmount,
TotalPaymentsReceived.Retentions,
TotalPaymentsReceived.TotalPayment,
TotalPaymentsReceived.Paidby,
TotalPaymentsReceived.InvoiceNo FROM
TotalPaymentsReceived;

TotalPaymentsReceived
SELECT ReceivePayments.InvoiceNo,
ReceivePayments.CheckNo,
ReceivePayments.CheckAmount, ReceivePayments.Retentions,
[CheckAmount]+[Retentions] AS TotalPayment,
ReceivePayments.Paidby
FROM ReceivePayments INNER JOIN InvoiceAging ON
ReceivePayments.InvoiceNo =
InvoiceAging.InvoiceNo;

Hope everything is clear and thanks for your help.
 
I was thinking on the same line, however I haven't had time to correct it.
I will give it a try and let you know. I will need to change the query
where the subreport comes from to select unpaid invoices from customers
instead of using the Invoice No. I ope that will also fix the problem with
the total sums. Thank you

Jesse Avilés
(e-mail address removed)

Duane Hookom said:
I think your error is "The subreport is linked to the main report by the
InvoiceNo". Shouldn't the link master/child be the CompanyID?

--
Duane Hookom
MS Access MVP


I have an invoice aging report with a payments received
subreport. Both
reports are based on two different queries. My main
report shows the aging
for each company, which it does nicely and trouble free.
However my
subreport does not show payments received from the
company. They are setup
so that each company will see a list of the invoices we
have sent along with
how far behind they are on their payments.

Report Header
Company Header
Detail
Company Footer
Subreport
Page Footer
Report Footer

The subreport is linked to the main report by the
InvoiceNo. I wanted to
show partial payments we have received for the various
invoices and on the
Grand Total for the company show the amount owed. The
current setup I have
is not working. Payments do not show on the report but
if I run the query I
can see the payments received. If I move the subreport
to the detail
section of the report, I can see partial payments for the
invoices we have
sent but then there will be a huge space between every
invoice listed (see
the accompanying snapshots). Another thing that is not
doing correctly is
the substraction of payments from the amount owed
(=IIf(TotalPaymentsReceivedSubreport.Report.HasData,(Nz
([TotalOutstandingInv
oices])-Nz(TotalPaymentsReceivedSubreport.Report!
SumOfTotalPayment)),[TotalO
utstandingInvoices])). It will always show the invoiced
amount and when I
run the report to show all the companies it will only
show the invoiced
amount of the last company. This is the SQL of the main
report:

SELECT InvoiceDesc.InvoiceNo, InvoiceDesc.ClientID,
InvoiceDesc.Company,
InvoiceDesc.InvoiceDate, InvoiceDesc.PurchaseOrderNumber,
Sum(InvDetTotPrice.LineTotal) AS SumOfLineTotal,
Invoices.Paid,
IIf(Date()-InvoiceDesc!InvoiceDate<31,Sum([LineTotal]))
AS [Current],
IIf(Date()-InvoiceDesc!InvoiceDate>30 And
Date()-InvoiceDesc!InvoiceDate<61,Sum([LineTotal])) AS
[31-60],
IIf(Date()-InvoiceDesc!InvoiceDate>60 And
Date()-InvoiceDesc!InvoiceDate<91,Sum([LineTotal])) AS
[61-90],
IIf(Date()-InvoiceDesc!InvoiceDate>90,Sum([LineTotal]))
AS [Over 90]
FROM (InvoiceDesc INNER JOIN Invoices ON
InvoiceDesc.InvoiceNo =
Invoices.InvoiceNo) INNER JOIN InvDetTotPrice ON
Invoices.InvoiceNo =
InvDetTotPrice.InvoiceNo
GROUP BY InvoiceDesc.InvoiceNo, InvoiceDesc.ClientID,
InvoiceDesc.Company,
InvoiceDesc.InvoiceDate, InvoiceDesc.PurchaseOrderNumber,
Invoices.Paid
HAVING (((Invoices.Paid)=False))
ORDER BY InvoiceDesc.InvoiceNo;

and this is the SQL of the subreport:
SELECT TotalPaymentsReceived.CheckNo,
TotalPaymentsReceived.CheckAmount,
TotalPaymentsReceived.Retentions,
TotalPaymentsReceived.TotalPayment,
TotalPaymentsReceived.Paidby,
TotalPaymentsReceived.InvoiceNo FROM
TotalPaymentsReceived;

TotalPaymentsReceived
SELECT ReceivePayments.InvoiceNo,
ReceivePayments.CheckNo,
ReceivePayments.CheckAmount, ReceivePayments.Retentions,
[CheckAmount]+[Retentions] AS TotalPayment,
ReceivePayments.Paidby
FROM ReceivePayments INNER JOIN InvoiceAging ON
ReceivePayments.InvoiceNo =
InvoiceAging.InvoiceNo;

Hope everything is clear and thanks for your help.
Click to expand...
 
Thanks, by using the company as the child/master link it
worked OK.
I'm still having trouble with the sum at the end of the
report. I have a textbox with that's supposed to show
the total amount of the report (invoices - payments). If
I print each report separately, the textbox will show the
sum correctly. However, if I run the whole report to
show all the companies, the texbox will show the sum only
for the last company of the report. This is the
expression in the textbox:
=IIf(TotalPaymentsReceivedSubreport.Report.HasData,(Nz
([TotalOutstandingInvoices])-Nz
(TotalPaymentsReceivedSubreport.Report!
SumOfTotalPayment)),[TotalOutstandingInvoices])

I tried using the following expression:
=Nz([TotalOutstandingInvoices])-Nz
(TotalPaymentsReceivedSubreport.Report!SumOfTotalPayment)
and it only works if there has been a payment received.
If there is no payment, the textbox will return an
#Error. Thanks for your help.

Jesse Avilés
(e-mail address removed)
http://home.coqui.net/monk

-----Original Message-----
I think your error is "The subreport is linked to the main report by the
InvoiceNo". Shouldn't the link master/child be the CompanyID?

--
Duane Hookom
MS Access MVP


I have an invoice aging report with a payments received
subreport. Both
reports are based on two different queries. My main
report shows the aging
for each company, which it does nicely and trouble free.
However my
subreport does not show payments received from the
company. They are setup
so that each company will see a list of the invoices we
have sent along with
how far behind they are on their payments.

Report Header
Company Header
Detail
Company Footer
Subreport
Page Footer
Report Footer

The subreport is linked to the main report by the
InvoiceNo. I wanted to
show partial payments we have received for the various
invoices and on the
Grand Total for the company show the amount owed. The
current setup I have
is not working. Payments do not show on the report but
if I run the query I
can see the payments received. If I move the subreport
to the detail
section of the report, I can see partial payments for the
invoices we have
sent but then there will be a huge space between every
invoice listed (see
the accompanying snapshots). Another thing that is not
doing correctly is
the substraction of payments from the amount owed
(=IIf(TotalPaymentsReceivedSubreport.Report.HasData,(Nz
([TotalOutstandingInv
oices])-Nz(TotalPaymentsReceivedSubreport.Report!
SumOfTotalPayment)),[TotalO
utstandingInvoices])). It will always show the invoiced
amount and when I
run the report to show all the companies it will only
show the invoiced
amount of the last company. This is the SQL of the main
report:

SELECT InvoiceDesc.InvoiceNo, InvoiceDesc.ClientID,
InvoiceDesc.Company,
InvoiceDesc.InvoiceDate, InvoiceDesc.PurchaseOrderNumber,
Sum(InvDetTotPrice.LineTotal) AS SumOfLineTotal,
Invoices.Paid,
IIf(Date()-InvoiceDesc!InvoiceDate<31,Sum([LineTotal]))
AS [Current],
IIf(Date()-InvoiceDesc!InvoiceDate>30 And
Date()-InvoiceDesc!InvoiceDate<61,Sum([LineTotal])) AS
[31-60],
IIf(Date()-InvoiceDesc!InvoiceDate>60 And
Date()-InvoiceDesc!InvoiceDate<91,Sum([LineTotal])) AS
[61-90],
IIf(Date()-InvoiceDesc!InvoiceDate>90,Sum([LineTotal]))
AS [Over 90]
FROM (InvoiceDesc INNER JOIN Invoices ON
InvoiceDesc.InvoiceNo =
Invoices.InvoiceNo) INNER JOIN InvDetTotPrice ON
Invoices.InvoiceNo =
InvDetTotPrice.InvoiceNo
GROUP BY InvoiceDesc.InvoiceNo, InvoiceDesc.ClientID,
InvoiceDesc.Company,
InvoiceDesc.InvoiceDate, InvoiceDesc.PurchaseOrderNumber,
Invoices.Paid
HAVING (((Invoices.Paid)=False))
ORDER BY InvoiceDesc.InvoiceNo;

and this is the SQL of the subreport:
SELECT TotalPaymentsReceived.CheckNo,
TotalPaymentsReceived.CheckAmount,
TotalPaymentsReceived.Retentions,
TotalPaymentsReceived.TotalPayment,
TotalPaymentsReceived.Paidby,
TotalPaymentsReceived.InvoiceNo FROM
TotalPaymentsReceived;

TotalPaymentsReceived
SELECT ReceivePayments.InvoiceNo,
ReceivePayments.CheckNo,
ReceivePayments.CheckAmount, ReceivePayments.Retentions,
[CheckAmount]+[Retentions] AS TotalPayment,
ReceivePayments.Paidby
FROM ReceivePayments INNER JOIN InvoiceAging ON
ReceivePayments.InvoiceNo =
InvoiceAging.InvoiceNo;

Hope everything is clear and thanks for your help.
--
Jesse Avilés
(e-mail address removed)
http://home.coqui.net/monk


.
Click to expand...
 
Just like you said. I used the Iif expression below at the Company footer,
set its running sum property to Over All, make non visible and then place a
new textbox at the report footer that pointed to the before mentioned
textbox. Thanks a lot. There should be a button in thsi forum that should
mark a thread with something like question answered. That way when someone
looks at the thread, it can see what worked and what didnt. Some thoughts.


Duane Hookom said:
Where is the text box? You probably need an extra copy in the Company footer
and name it "txtCoTotal" and set its running sum to over report. Then add a
text box to the report footer with a control source of =txtCoTotal.

--
Duane Hookom
MS Access MVP


Thanks, by using the company as the child/master link it
worked OK.
I'm still having trouble with the sum at the end of the
report. I have a textbox with that's supposed to show
the total amount of the report (invoices - payments). If
I print each report separately, the textbox will show the
sum correctly. However, if I run the whole report to
show all the companies, the texbox will show the sum only
for the last company of the report. This is the
expression in the textbox:
=IIf(TotalPaymentsReceivedSubreport.Report.HasData,(Nz
([TotalOutstandingInvoices])-Nz
(TotalPaymentsReceivedSubreport.Report!
SumOfTotalPayment)),[TotalOutstandingInvoices])

I tried using the following expression:
=Nz([TotalOutstandingInvoices])-Nz
(TotalPaymentsReceivedSubreport.Report!SumOfTotalPayment)
and it only works if there has been a payment received.
If there is no payment, the textbox will return an
#Error. Thanks for your help.

Jesse Avilés
(e-mail address removed)
http://home.coqui.net/monk

-----Original Message-----
I think your error is "The subreport is linked to the main report by the
InvoiceNo". Shouldn't the link master/child be the CompanyID?

--
Duane Hookom
MS Access MVP


I have an invoice aging report with a payments received
subreport. Both
reports are based on two different queries. My main
report shows the aging
for each company, which it does nicely and trouble free.
However my
subreport does not show payments received from the
company. They are setup
so that each company will see a list of the invoices we
have sent along with
how far behind they are on their payments.

Report Header
Company Header
Detail
Company Footer
Subreport
Page Footer
Report Footer

The subreport is linked to the main report by the
InvoiceNo. I wanted to
show partial payments we have received for the various
invoices and on the
Grand Total for the company show the amount owed. The
current setup I have
is not working. Payments do not show on the report but
if I run the query I
can see the payments received. If I move the subreport
to the detail
section of the report, I can see partial payments for the
invoices we have
sent but then there will be a huge space between every
invoice listed (see
the accompanying snapshots). Another thing that is not
doing correctly is
the substraction of payments from the amount owed
(=IIf(TotalPaymentsReceivedSubreport.Report.HasData,(Nz
([TotalOutstandingInv
oices])-Nz(TotalPaymentsReceivedSubreport.Report!
SumOfTotalPayment)),[TotalO
utstandingInvoices])). It will always show the invoiced
amount and when I
run the report to show all the companies it will only
show the invoiced
amount of the last company. This is the SQL of the main
report:

SELECT InvoiceDesc.InvoiceNo, InvoiceDesc.ClientID,
InvoiceDesc.Company,
InvoiceDesc.InvoiceDate, InvoiceDesc.PurchaseOrderNumber,
Sum(InvDetTotPrice.LineTotal) AS SumOfLineTotal,
Invoices.Paid,
IIf(Date()-InvoiceDesc!InvoiceDate<31,Sum([LineTotal]))
AS [Current],
IIf(Date()-InvoiceDesc!InvoiceDate>30 And
Date()-InvoiceDesc!InvoiceDate<61,Sum([LineTotal])) AS
[31-60],
IIf(Date()-InvoiceDesc!InvoiceDate>60 And
Date()-InvoiceDesc!InvoiceDate<91,Sum([LineTotal])) AS
[61-90],
IIf(Date()-InvoiceDesc!InvoiceDate>90,Sum([LineTotal]))
AS [Over 90]
FROM (InvoiceDesc INNER JOIN Invoices ON
InvoiceDesc.InvoiceNo =
Invoices.InvoiceNo) INNER JOIN InvDetTotPrice ON
Invoices.InvoiceNo =
InvDetTotPrice.InvoiceNo
GROUP BY InvoiceDesc.InvoiceNo, InvoiceDesc.ClientID,
InvoiceDesc.Company,
InvoiceDesc.InvoiceDate, InvoiceDesc.PurchaseOrderNumber,
Invoices.Paid
HAVING (((Invoices.Paid)=False))
ORDER BY InvoiceDesc.InvoiceNo;

and this is the SQL of the subreport:
SELECT TotalPaymentsReceived.CheckNo,
TotalPaymentsReceived.CheckAmount,
TotalPaymentsReceived.Retentions,
TotalPaymentsReceived.TotalPayment,
TotalPaymentsReceived.Paidby,
TotalPaymentsReceived.InvoiceNo FROM
TotalPaymentsReceived;

TotalPaymentsReceived
SELECT ReceivePayments.InvoiceNo,
ReceivePayments.CheckNo,
ReceivePayments.CheckAmount, ReceivePayments.Retentions,
[CheckAmount]+[Retentions] AS TotalPayment,
ReceivePayments.Paidby
FROM ReceivePayments INNER JOIN InvoiceAging ON
ReceivePayments.InvoiceNo =
InvoiceAging.InvoiceNo;

Hope everything is clear and thanks for your help.
--
Jesse Avilés
(e-mail address removed)
http://home.coqui.net/monk


.
Click to expand...
Click to expand...
 
Back
Top