Summing visible controls only

[Guest]

Hi

I am trying to produce an aged trial balance report which should give me the
outstanding accounts per each debtor, and print the outstanding amount in the
relevant column, Current, 30 Days, 60 Days, 90 Days. The report is Grouped
by Debtor.

I have the following code in the On Format event in the Details section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Select Case (Date - TRANS_DATE)
Case 0 To 30
Me!OUTAMTC.Visible = True
Me!OUTAMT30.Visible = False
Me!OUTAMT60.Visible = False
Me!OUTAMT90.Visible = False
Case 31 To 59
Me!OUTAMTC.Visible = False
Me!OUTAMT30.Visible = True
Me!OUTAMT60.Visible = False
Me!OUTAMT90.Visible = False
Case 60 To 89
Me!OUTAMTC.Visible = False
Me!OUTAMT30.Visible = False
Me!OUTAMT60.Visible = True
Me!OUTAMT90.Visible = False
Case Else
Me!OUTAMTC.Visible = False
Me!OUTAMT30.Visible = False
Me!OUTAMT60.Visible = True
Me!OUTAMT90.Visible = False
End Select

End Sub

This seems to work well, with the outstanding amounts appearing in the
relevant columns, as below:

Debtor Date Invoice InvoiceAmt Current 30Days 60Days
90Days
Debtor1
11 Dec 04 Invoice1 $100
$100
12 Jan 05 Invoice2 $200 $200

My problem is thus: I want to add a total to each group summing each column
(Current, 30 Days, 60 Days, 90 Days). However, I know the text boxes are
actually displaying the figures across each record, despite being hidden and
therefore I'm getting the following if the text boxes are all visible:

Debtor Date Invoice InvAmt Current 30Days 60Days 90Days
Debtor1
11 Dec 04 Invoice1 $100 $100 $100 $100
$100
12 Jan 05 Invoice2 $200 $200 $200 $200
$200

Hence when I try to total each column, I get $300 in each column instead of
getting $200 in the 30Days column total and $100 in the 90Days column total.

Is there a way around this, or am I doing it all wrong to begin with?

Thanking you in advance.

 

[Duane Hookom]

Not exactly the method that I would use but if it works for you... I would
probably use a crosstab query.

You should be able to add a text box in a group or report footer to get the
totals like:
= Sum( Abs( (Date-TRANS_DATE) Between 0 AND 30) * InvoiceAmt )

 

[Guest]

Hi Duane

Thanks for your reply.

I would have used a crosstab query too, but I'm working with an adp, and I'm
not quite sure how to create a stored procedure that does a similar thing.

I'll give what you suggested a go and see what happens.

Regards

 

[Duane Hookom]

You can place your "Case" logic into your SQL. This puts your processing
load on the sql server where it belongs.

SELECT Debtor, Date, Invoice, Sum(InvoiceAmt) as SumInvoice,
Sum(CASE WHEN DateDiff(day, TRANS_DATE, GetDate()) BETWEEN 0 AND 30 THEN
InvoiceAmt ELSE 0 END) As QtyLast30,
Sum(CASE WHEN DateDiff(day, TRANS_DATE, GetDate()) BETWEEN 31 AND 60 THEN
InvoiceAmt ELSE 0 END) As Qty31_60,
Sum(CASE WHEN DateDiff(day, TRANS_DATE, GetDate()) BETWEEN 61 AND 90 THEN
InvoiceAmt ELSE 0 END) As Qty61_90
FROM tblYourTable
WHERE DateDiff(day, TRANS_DATE, GetDate()) <91
GROUP BY Debtor, Date, Invoice

 

[Guest]

Thanks heaps Duane! With a bit of tweaking, your code worked like a charm!
I'm a bit of a novice at the SQL thing.

Sorry for the delayed reply, I only work part-time, so couldn't test your
suggestions until today.

Thanks again!

Regards