and yet another aging report problem

[Angi]

I've read about 40 of the 61 postings about aging reports, and couldn't
find my problem. Please forgive me if this has been covered.

I have a statement that lists all open invoices and payments for a
customer with a balance. Works fine. I've created a subreport in
report footer for aging totals with the query StmtSubreport as it's
record source. My problem is a couple of things:

1 - My totals are giving me the acct total due instead of the aging
group total.

2 - It's listing the columns and totals for each invoice, rather than
the totals I thought I was asking for. There should only be one
line...not a line for each invoice.

Can someone please tell me what I'm doing wrong? TIA

Subreport record source:
SELECT DSum("amtremaining","invoicemain","coid=" & [coid]) AS TotDue,
IIf(DateDiff("d",[orderdate],Date())<=30,DSum("amtremaining","invoicemain","coid="
& [coid])) AS CurrentDue, IIf(DateDiff("d",[orderdate],Date()) Between
31 And 60,DSum("amtremaining","invoicemain","coid=" & [coid])) AS
ThirtyDays, IIf(DateDiff("d",[orderdate],Date()) Between 61 And
90,DSum("amtremaining","invoicemain","coid=" & [coid])) AS SixtyDays,
IIf(DateDiff("d",[orderdate],Date())>90,DSum("amtremaining","invoicemain","coid="
& [coid])) AS NinetyDays, invoicemain.CoID
FROM invoicemain;

 

[Duane Hookom]

Try this query for your subreport record source:

SELECT COID, Sum(AmtRemaining) as TotDue,
Sum(Abs(DateDiff("d",[orderdate],Date())<=30) * AmtRemaining) As CurrentDue,
Sum(Abs(DateDiff("d",[orderdate],Date()) Between 31 And 60) * AmtRemaining)
as ThirtyDays,
Sum(Abs(DateDiff("d",[orderdate],Date()) Between 61 And 90) * AmtRemaining)
as SixtyDays,
Sum(Abs(DateDiff("d",[orderdate],Date())>90) * AmtRemaining) As NinetyDays
FROM InvoiceMain
GROUP BY COID

This will create one line per COID.
I believe you can use link master/child with the COID field.

 

[Angi]

Duane,
That's a beautiful thing!! Thank you!! I always forget about that
GROUP BY!! I have only one issue. If there is nothing for a
particular aging group, I need it to be blank. Right now it's $0.00.
How do I fix that?

TY,
Ang

 

[Duane Hookom]

Rather than muck up a "beautiful thing", you can set the format of your text
box in your report to display nothing where the value is 0.

Check Help on "Format Numeric".

 

[Angi]

Duane,
I figured that's what you were going to say so I went had gone ahead
and created a custom format. $#,##0.00;;"";""

Thanks for all your help!

Angi