second time lucky?

  • Thread starter Thread starter Joe Bohen
  • Start date Start date
J

Joe Bohen

I asked a question on 25 September, I wanted to show all
detail lines for invoices on a table but only show the
invoice total (tnett) once. I have managed to provide part
of the answer myself I have grouped the report on invoice
numbers hiding the detail I don't require and placed two
unbound text boxes on the reports detail section. The
first has a control source of 1 and is set to a running
sum across the group, the second shows the invoice total
(tnett) only if the first unbound text box has a value =
to 1. This is achieved by the following event on the on
format of the detail section.
invnumber = Text57
If (invnumber) = 1 Then
Text58 = tnett
Else
Text58 = 0
My problem now is how to total the values in text58 for
the whole report. If I try to sum text58 I get an error in
the box.
Any support much appreciated.
Joe
 
First you suggest "I wanted to show all detail lines" then you stated
"hiding the detail I don't require". This two statements don't make sense to
me. Either you show them all or you show some or you show none.

If you typed a few records (group and detail sections) into a posting and
showed what values you wanted to total where, it would probably be a snap
for several of us to answer.

If a picture is worth a thousand words, an example is worth a few dozen
postings.
 
Joe,
IT appears that you are using one report that has the Invoice and Invoice
Details combined into one query.
Instead. use a report/subreport
The main Report would show Invoice-level info - name, address, Subtotal,
Taxes, GrandTotal

The subreport would contain invoice lines and in the footer an invisible
control containing the sum of
invoice lines - you can refer to this in your main report if you need the
subtotal from the lines

Regards
HS
 
Thanks for both your reply's. I think that HS's method of
building in a sub report structure is the most likely way
forward although I feel that if I can articulate my
problem, then there must be a method to achieve the result
without resorting to a sub report. My problem stems from
the fact that I have no control over the material I have
to work with. The underlying DBF 4 tables are generated
from a third party programme, which generates two tables;
invoice details and invoice line detail. Unfortunately the
line detail does not provide individual line costs, merely
the stock descriptions these I have pulled together
through a query, which provides the basis for the report.
Hence the duplication of any invoices value, where more
than one item of stock is posted. See example.

Invoice Date stock Net value
123 1/09/03 stock 1 10.00
124 2/09/03 stock 1 30.00
124 2/09/03 stock 2 30.00
125 2/09/03 stock 1 50.00
Total: 120.00

As you can see invoice 124 has two stock items posted and
shows two net values and distorts the true total. What I
would like to achieve is.

Invoice Date stock Net value
123 1/09/03 stock 1 10.00
124 2/09/03 stock 1 30.00
124 2/09/03 stock 2
125 2/09/03 stock 1 50.00
Total: 90.00

I have got the report to show the table as above apart
from the Total calculation by; Grouping on the Invoice
field and turning the Net Value fields visible property
off, then I have placed two unbound text boxes on the
report the first one counts the number of lines on each
invoice (text57) the second is set to show net value only
where text57 has a value of 1(text58). My problem is that
I cannot get the Total of the text box (text58) to
calculate. Text58 is calculated on the format event of the
invoice detail section of the report with the following
code.

Dim invnumber as Variant

invnumber = Text57
If (invnumber) = 1 Then
Text58 = tnett
Else
Text58 = 0

Hope you can follow my picture and that I have not used to
narrow a lens!
Joe
 
If I understand correctly, you need to create a group heading for Invoice.
Add a new text box in this group header
Name: txtValueRunSum
Control Source: [Net Value]
Running Sum: Over All
Visible: No
Then, in the report footer, add a text box
Control Source: =txtValueRunSum
 
Back
Top