Customer Statement Report:

[msrobotto]

Past Due Boxes: 30/60/90/etc

Hi! I am working on creating an Accounts Receivable database, and I want to be able to automatically generate statements for my customers. The statements need to list all open invoices (this works), total all invoices (this works), and have boxes at the bottom that show amounts currently due, 30 days past due, 60 days past due, etc. (the sticky wicket)

When I try to use the DSUM function I can only generate one statement at a time. If I try to run a batch of statements the boxes show the totals for the whole report, and not for each customer.

When I use subreports for the totals it seems to work. I view the report on screen and everything adds up perfectly. If a customer has no invoices that are 60 days past due it shows $0.00 in the box as it should. However, when I try to print the report (to the printer, to a PDF, or simply print preview) all the boxes with $0.00 dissappear. I know it is a null value error, but I don't understand why it shows up on the screen perfectly and won't print.

I'm telling you, Skynet won't need giant robots, the machines will simply annoy us to death. ;)

Any help would be greatly appreciated!!

~Vicki :)

[puppydogbuddy]

Vicki,
<<<When I try to use the DSUM function I can only generate one statement at a time. If I try to run a batch of statements the boxes show the totals for the whole report, and not for each customer>>>>
All you needed to do was use the sorting/grouping tool in the report designer to create a footer on customer. Then you could use the summation formula on the amount field in the customer footer as shown.>> = Sum([theAmountField])
and there would have been no need for a subreport. Try it and let me know.

[puppydogbuddy]

PS: If you have nulls, you can do the formula this way:
>>>>>>> = nz(Sum([theAmountField],0))

[msrobotto]

Vicki,
<<<When I try to use the DSUM function I can only generate one statement at a time. If I try to run a batch of statements the boxes show the totals for the whole report, and not for each customer>>>>
All you needed to do was use the sorting/grouping tool in the report designer to create a footer on customer. Then you could use the summation formula on the amount field in the customer footer as shown.>> = Sum([theAmountField])
and there would have been no need for a subreport. Try it and let me know.

Thanks puppydogbuddy, but that only gives me the total of ALL invoices for each customer, which I already knew how to do.

total all invoices (this works)

After that, at the bottom of the statement for each individual customer, I need sub-totals for:

all of the invoices that are currently due
all of the invoices that are 31-60 days past due
all of the invoices that are 61-90 days past due
all of the invoices that are 91-120 days past due
all of the invoices that are 121 days past due or more

The regular SUM function loses this detail, the DSUM can't be restricted to two parameters ('Customer' and 'DaysPastDue'), and since a large number of customers pay their bills before the bills are 120 days past due that field is often (but not always) null in a SUBREPORT- which makes the box dissappear entirely (and looks terribly sloppy).

I wound up deciding to cheat and hard coded a "0.00" value in a text box behind the subreport. If there is a value the subreport box prints and the zero can't be seen. If there is a null the box disappears and the zero behind it prints.

Primitive, but effective.

[NeoPa]

Vicki,

Do you have a field that determines the date that needs to be checked to determine which box to assign the value to?

You probably need something like the following for the three boxes :

=Nz(Sum(IIf(Date() Between DateAdd('d',30,[InvDate]) And DateAdd('d',59,[InvDate]),[theAmountField],0)),0)
=Nz(Sum(IIf(Date() Between DateAdd('d',60,[InvDate]) And DateAdd('d',89,[InvDate]),[theAmountField],0)),0)
=Nz(Sum(IIf(Date()>=DateAdd('d',90,[InvDate]),[theAmountField],0)),0)

Welcome to Bytes!

[NeoPa]

I posted my last before your last Vicki.

Nevertheless a few tweaks should give you what you need.

NB. DSum() can be restricted to two parameters ('Customer' and 'DaysPastDue'). It is far better practice to use Sum() where possible though. You will notice the performance difference in most cases.