Viewing a sum of unpaid reports

[RichStone]

I have created a subform that displays a list of invoices for the client the subform relates to. Each entry shows either the date the invoice was paid or the text UNPAID in a field. In the footer of the subform I have a counter for the number of invoices displayed and the sum of all invoices. This is working perfectly using the following formulae (Field name given first):

InvCount: =count([AccRef])
TotInvoiced: =sum([Amount])

I would like to add another field showing the sum of unpaid invoices. I created a new field with the following formula:

TotUnpaid: =Sum([Amount] And [PaidDate]="UNPAID")

This is not working. Can somebody please tell me what I am doing wrong?

 

[Minty]

Sum() adds a single field value up, you can't add criteria. You will need to use a DSum

DSum("Amount" ,"YourPaymentTableName" , "[PaidDate]='UNPAID'")

I do have a warning light going off though. A field called PaidDate should only ever have dates in it. If there is no payment date by definition it's unpaid. You must therefore be storing text, which will lead to all sorts of problems.

 

[RichStone]

Thanks for your assistance, but I'm still having issues...

Firstly, the PaidDate field is for visual use only, hence it shows either a date that has been converted to text, or the the label "UNPAID" so that is not a concern.

The date on the subform runs from a query and not a table. Basically, the invoices are in a separate table and the query filters the data based on the subform's parent details (client name field) and sorted in decending order. It appears that I may have the reference to the date incorrect as it won't allow the subform name or the query name it's based on.

Any ideas?

 

[Minty]

Where I put "YourTableNAme" you can replace with a query name. If PaidDate is a calculated field you can simply refer to the original date field in the query "[DateField] is Null" assuming it is null.

 

[RichStone]

That's what I tried. First I tried the name of the query the subform is based on and when that didn't work I tried the subform name iteself which still didn't work.

[PaidDate] is not calculated. It is either the textual date or UNPAID. That is why I just wanted to check whether it was UNPAID or not.

 

[Minty]

When you say doesn't work, what isn't working? Error code wrong totals etc.

Also if you have Parent Child records set up correctly on the sub form you shouldn't normally need to use a query to also filter those records.

 

[RichStone]

Apologies for the lack of response. I was pulled from this task for a while, but I'm back onto it now. I came up with a better idea but still can't seem to get it to work. I have now created a numeric field for each entry that is calculated on loading and is either the invoice balance if not paid and is 0 if it is. It appears to display correctly for entries but it still won't total in the summary.

The new field is called 'Owing' and is set to display as currency. I am using sum([Owing]) as the details in the field 'TotOwing' in the summary in exactly the same way as I'm using sum([Amount]) which works fine. However the 'TotOwing' field just shows '#Error' and weirdly enough causes the other summary field show the same.

So much for a fresh approach! Any ideas?

 

[Minty]

Is the Owing field calculated on the form or in the underlying query ? Do it in the query and you will be able to use it it further calculations on the form.

Otherwise you have to use the actual base level calculation in the sum() which gets messy if there are null values.

 

[RichStone]

SOLVED!

I thought of that yesterday afternoon and placed the formula into the query. It worked! Thanks for pointing me in that direction. It does seem that sums can only be done on precalculated values in the entries.