By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,653 Members | 1,774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,653 IT Pros & Developers. It's quick & easy.

Customer Statement Report:

P: 7
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 :)
Jun 10 '09 #1
Share this Question
Share on Google+
5 Replies


puppydogbuddy
Expert 100+
P: 1,923
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.
Jun 12 '09 #2

puppydogbuddy
Expert 100+
P: 1,923
PS: If you have nulls, you can do the formula this way:
>>>>>>> = nz(Sum([theAmountField],0))
Jun 12 '09 #3

P: 7
@puppydogbuddy

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.
Jun 12 '09 #4

NeoPa
Expert Mod 15k+
P: 31,712
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 :
Expand|Select|Wrap|Line Numbers
  1. =Nz(Sum(IIf(Date() Between DateAdd('d',30,[InvDate]) And DateAdd('d',59,[InvDate]),[theAmountField],0)),0)
  2. =Nz(Sum(IIf(Date() Between DateAdd('d',60,[InvDate]) And DateAdd('d',89,[InvDate]),[theAmountField],0)),0)
  3. =Nz(Sum(IIf(Date()>=DateAdd('d',90,[InvDate]),[theAmountField],0)),0)
Welcome to Bytes!
Jun 12 '09 #5

NeoPa
Expert Mod 15k+
P: 31,712
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.
Jun 12 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.