Total in Report

[Joan]

Hi,
I am working on a report (InvoiceStatement) where I recently added a 3rd
subreport. However, I am having problems with the report total, which
totals the 3 sub-reports' totals. Before I added the 3rd subreport,
InvoiceAdjustments, the following main report total's control source worked
fine: Reports!rptInvoiceStatement!rptInvoiceDogs!txtSumSalesPrice +
Nz(Reports!rptInvoiceStatement!rptInvoiceCharges!txtSumTotCharge, 0).

The 3rd subreport displays any adjustments to the invoice with some to many
invoices not having any adjustments at all. In these cases, the subreport
doesn't need to print and there is no subreport total to add to the main
report total. How do I write the control source for the Total on the main
report??

I tried the following:
Reports!rptInvoiceStatement!rptInvoiceDogs!txtSumSalesPrice +
Nz(Reports!rptInvoiceStatement!rptInvoiceCharges!txtSumTotCharge, 0) +
Nz(Reports!rptInvoiceStatement!rptInvoiceAdjustments!txtAdjTot, 0).

When I run the InvoiceStatement, the invoices without any adjustments do not
print the final total, instead it prints #Error. However, for the invoice
with adjustments, it prints the correct total.


Joan

 

[Steve Schapel]

Joan,

If there is no data in the subreport, then txtAdjTot will not be Null,
it will not exist. There are a couple of ways of handling this.
Here's one... In a Module, enter the following code to produce a
user-defined function:

Public Function nnz(testvalue As Variant) As Variant
If Not (IsNumeric(testvalue)) Then
nnz = 0
Else
nnz = testvalue
End If
End Function

Also, you are using redundant references in the expression, since it
is already on the rptInvoiceStatement report. So...
=[rptInvoiceDogs]![txtSumSalesPrice]+Nz([rptInvoiceCharges]![txtSumTotCharge],0)+nnz([rptInvoiceAdjustments]![txtAdjTot,])

- Steve Schapel, Microsoft Access MVP

 

[Joan]

Thanks so much, Steve! I used the public functiion and called it from my
control source statement like you advised and it works great! Since you are
familiar with what I am doing, would you mind answering another question?

I have two textboxes in the Detail section of the main report, txtTotal and
txtPay. txtTotal reflects the total of the first 2 subreports, whereas
txtPay reflects the total of all 3 subreports (including adjustments).
txtPay's control source is the one that you helped me with. If there are no
adjustments, instead of showing both txtTotal and txtPay, I would like to
just show txtPay and not show lblTotal or txtTotal. I tried checking for
the value of nnz(rptInvoiceAdjustments!txtAdjTot) in both the OnFormat and
the OnPrint events of the Detail section(of the main report):

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim adj As Variant
adj = Me![rptInvoiceAdjustments]![txtAdjTot]
If nnz(adj) = 0 Then
Me!lblTotal.Visible = False
Me!txtTotal.Visible = False
End If
End Sub

However, I get a run-time error: "You entered an expression that has no
value". When I debugg, the line: adj =
Me![rptInvoiceAdjustments]![txtAdjTot] is highlighted. Should I have
my code in either a different section or event in the report? What is the
best way of doing this?

Joan

Joan,

If there is no data in the subreport, then txtAdjTot will not be Null,
it will not exist. There are a couple of ways of handling this.
Here's one... In a Module, enter the following code to produce a
user-defined function:

Public Function nnz(testvalue As Variant) As Variant
If Not (IsNumeric(testvalue)) Then
nnz = 0
Else
nnz = testvalue
End If
End Function

Also, you are using redundant references in the expression, since it
is already on the rptInvoiceStatement report. So...
=[rptInvoiceDogs]![txtSumSalesPrice]+Nz([rptInvoiceCharges]![txtSumTotCharge
],0)+nnz([rptInvoiceAdjustments]![txtAdjTot,])

- Steve Schapel, Microsoft Access MVP

Hi,
I am working on a report (InvoiceStatement) where I recently added a 3rd
subreport. However, I am having problems with the report total, which
totals the 3 sub-reports' totals. Before I added the 3rd subreport,
InvoiceAdjustments, the following main report total's control source worked
fine: Reports!rptInvoiceStatement!rptInvoiceDogs!txtSumSalesPrice +
Nz(Reports!rptInvoiceStatement!rptInvoiceCharges!txtSumTotCharge, 0).

The 3rd subreport displays any adjustments to the invoice with some to many
invoices not having any adjustments at all. In these cases, the subreport
doesn't need to print and there is no subreport total to add to the main
report total. How do I write the control source for the Total on the main
report??

I tried the following:
Reports!rptInvoiceStatement!rptInvoiceDogs!txtSumSalesPrice +
Nz(Reports!rptInvoiceStatement!rptInvoiceCharges!txtSumTotCharge, 0) +
Nz(Reports!rptInvoiceStatement!rptInvoiceAdjustments!txtAdjTot, 0).

When I run the InvoiceStatement, the invoices without any adjustments do not
print the final total, instead it prints #Error. However, for the invoice
with adjustments, it prints the correct total.


Joan

 

[Steve Schapel]

Joan,

Try it like this...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim adj As Boolean
adj = nnz(Me![rptInvoiceAdjustments].Report!txtAdjTot)=0
If adj Then
Me!lblTotal.Visible = False
Me!txtTotal.Visible = False
End If
End Sub

I think it is better in the Format event. I am not certain that the
other changes I made will fix the problem, but if not, post back and
we'll look at it again.

By the way, if lblTotal is attached to txtTotal, I don't think you
will need to explicitly toggle its Visible property, it should just
follow the lead of the textbox.

An alternative way of doing it (watch for newsreader wordwrap!)...
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.txtTotal.Visible =
(nnz(Me.rptInvoiceAdjustments.Report!txtAdjTot)<>0)
End Sub

- Steve Schapel, Microsoft Access MVP

 

[Joan]

Steve,

Thanks once again! I used the public function like you recommended ( only I
added: Else
Me!Line135.Visible = True
Me!lblTotal.Visible = True
Me!txtTotal.Visible = True)

and it works wonderfully. The report now works exactly like I want on
my machine. However, I took it out to the the client's office today and
when I try to open the report there I get the message : "Enter parameter
value: nnz( )"
Don't know why it is doing this. The client has Access 2003 whereas my
machine has 2002 on it. The application is in Access 2000 format. I can't
tell anything by looking at the References. They seem OK. Any ideas why it
might be doing this?

Also I noticed while I was there that the application was running much
slower than usual even after compacting and repairing. So then I tried
opening up a blank database and importing all the queries, forms, reports
and modules. Then however, it seems some of my queries did not import
correctly, as they have many Expr's: in them. For instance, the tables
were still in the QBE pane, but joins were missing. Is there something I
should be doing when I import queries,so that my application will not do
this?

The clients application was running so slow, that many times I didn't even
have a cursor. So it was difficult to get in design mode and do anything.
Needless to say, the morning was very frustrating for me. Any tips?

Thanks again for all your help!

Joan

Joan,

Try it like this...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim adj As Boolean
adj = nnz(Me![rptInvoiceAdjustments].Report!txtAdjTot)=0
If adj Then
Me!lblTotal.Visible = False
Me!txtTotal.Visible = False
End If
End Sub

I think it is better in the Format event. I am not certain that the
other changes I made will fix the problem, but if not, post back and
we'll look at it again.

By the way, if lblTotal is attached to txtTotal, I don't think you
will need to explicitly toggle its Visible property, it should just
follow the lead of the textbox.

An alternative way of doing it (watch for newsreader wordwrap!)...
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.txtTotal.Visible =
(nnz(Me.rptInvoiceAdjustments.Report!txtAdjTot)<>0)
End Sub

- Steve Schapel, Microsoft Access MVP

Thanks so much, Steve! I used the public functiion and called it from my
control source statement like you advised and it works great! Since you are
familiar with what I am doing, would you mind answering another question?

I have two textboxes in the Detail section of the main report, txtTotal and
txtPay. txtTotal reflects the total of the first 2 subreports, whereas
txtPay reflects the total of all 3 subreports (including adjustments).
txtPay's control source is the one that you helped me with. If there are no
adjustments, instead of showing both txtTotal and txtPay, I would like to
just show txtPay and not show lblTotal or txtTotal. I tried checking for
the value of nnz(rptInvoiceAdjustments!txtAdjTot) in both the OnFormat and
the OnPrint events of the Detail section(of the main report):

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim adj As Variant
adj = Me![rptInvoiceAdjustments]![txtAdjTot]
If nnz(adj) = 0 Then
Me!lblTotal.Visible = False
Me!txtTotal.Visible = False
End If
End Sub

However, I get a run-time error: "You entered an expression that has no
value". When I debugg, the line: adj =
Me![rptInvoiceAdjustments]![txtAdjTot] is highlighted. Should I have
my code in either a different section or event in the report? What is the
best way of doing this?

Joan

 

[Steve Schapel]

Joan,

I am afraid I don't have any difinitive ideas about the problem with
your database on the other computer. But it sounds nasty! Do other
Access databases run ok on your client's machine? Are you linking to
backend data tables over a network? If you take a copy of the
problematic file back to your own machine, is it ok? In the case of a
query with the disappearing joins, are the joins still included if you
look at the SQL view of the query? These are only random thoughts in
the hope that it might help narrow the scope of where to look for the
source of the problem. If you don't get it sorted, maybe a new
thread, possibly in the microsoft.public.access newsgroup, would be a
good idea.

- Steve Schapel, Microsoft Access MVP

 

[Joan]

Steve,

I got the performance issue solved anyway. I imported the objects into a
blank database from an uncorrupted file at home. I believe the one at the
client's had become corrupted (and this is what I used to import from the
first time I tried it). Then I converted it to Access 2002, compiled it and
saved it. I then made a copy of it and brought it out to the client's
office. The speed is great and everything is working as it should except I
now get a run-time error when I run the rptInvoiceStatement that states: "
Run-time error '2455: You entered an expression that has an invalid
reference to the property Form/Report." When I debugg, the following line
is highlighted: adj= nnz(me![rptInvoiceAdjustments].Report!txtAdjTot) = 0.

I don't get this message on my machine at home when I link to sample data in
a different back-end database. But when I use the client's back-end
database data on my machine, I get the same run-time error. What could this
possibly mean?

Joan

 

[Joan]

Steve,

I figured out why I was getting the error message and thought you might be
curious about what the problem was. The source of the problem was in the
data in my clients tables. In the many nested queries for the report, I had
equal joined the Invoices table to the Shippers table. However, the client
had not entered the ShipperCode into the Shippers table that the tables were
joined on. I fixed this by doing an outer join here so that all invoices
would be included in the query whether or not the ShipperCode was entered in
the table. Now my report works fine.

Joan

Steve,

I got the performance issue solved anyway. I imported the objects into a
blank database from an uncorrupted file at home. I believe the one at the
client's had become corrupted (and this is what I used to import from the
first time I tried it). Then I converted it to Access 2002, compiled it and
saved it. I then made a copy of it and brought it out to the client's
office. The speed is great and everything is working as it should except I
now get a run-time error when I run the rptInvoiceStatement that states: "
Run-time error '2455: You entered an expression that has an invalid
reference to the property Form/Report." When I debugg, the following line
is highlighted: adj= nnz(me![rptInvoiceAdjustments].Report!txtAdjTot) = 0.

I don't get this message on my machine at home when I link to sample data in
a different back-end database. But when I use the client's back-end
database data on my machine, I get the same run-time error. What could this
possibly mean?

Joan

Joan,

I am afraid I don't have any difinitive ideas about the problem with
your database on the other computer. But it sounds nasty! Do other
Access databases run ok on your client's machine? Are you linking to
backend data tables over a network? If you take a copy of the
problematic file back to your own machine, is it ok? In the case of a
query with the disappearing joins, are the joins still included if you
look at the SQL view of the query? These are only random thoughts in
the hope that it might help narrow the scope of where to look for the
source of the problem. If you don't get it sorted, maybe a new
thread, possibly in the microsoft.public.access newsgroup, would be a
good idea.

- Steve Schapel, Microsoft Access MVP

want

on

why

it

 

[Steve Schapel]

Joan,

Thanks a lot for letting us know, and my apologies that I didn't
respond to your previous post. Congratulations on your successful
trouble-shooting efforts.

- Steve Schapel, Microsoft Access MVP