DSUM Syntax in Report

[gray]

HI All

Acc2003

I have a report populated by a query of an Invoices table. This query lists all invoices for a given customer. The customer's ID is passed to the query via a pop-up customer-selection form. The selection form calls the query and then the report when "OK" button is clicked.

The report's page header has a textbox containing the customer_ID

The detail section shows the
1. "invoice_number",
2. "Invoice_Value" (currency)
3. "Date_Issued"
4. "Date_Payment_Recd" (can be NULL).

The report footer has a DSUM totals box for all the invoices shown.

All easy stuff so far...

I want a 2nd textbox on the report footer which Dsums all the invoices that HAVE been paid... i.e. where the "date_payment_recd" is NOT Null.

You can probably guess the next bit... a 3rd textbox with the amount oustanding (i.e. total amount invoiced minus total amount rec'd)

Here's my latest attempt at Dsumming the 2nd textbox

=NZ(DSum("Invoice_Value", "Invoices", "[Customer_No] = " & ![Customer_No_TextBox] & "[Date_Payment_Recd]=NULL"))

All I get is #ERROR..... I know Report textboxes work slightly differently to Form textboxes but can anyone see the problem?

Cheers

 

[Galaxiom]

"anthing = Null" will always return False, even "Null = Null" because there isn't anything that equals Null, not even itself.

Some places you can use "fieldname Is Null"

Elsewhere use the IsNull function. IsNull(whatever)

 

[vbaInet]

Just use the Sum() function on the payment field. It ignores Nulls.

For your third problem, do the same again, Sum()

 

[CBrighton]

Just use the Sum() function on the payment field. It ignores Nulls.

For your third problem, do the same again, Sum()

This.

Rather than accessing another copy of the records each time you use a domain aggregate function, just sum the fields in the recordsource.

You can always tweak the query to have extra fields for the summing if required, e.g.:

PaidValue: Iif([InvoicePaid]=True,[InvoiceAmount],0)

The above field could then be summed without domain aggregate functions to give the sum of the values of the invoices which have been paid.

Similarly a field with False instead of true could be summed to give the total of unpaid invoices.

 

[gray]

Hi Gents

Thanks for the replies. I'm getting closer...

I've added a textbox to the detail section with:-

 Name=Paid_Value
ControlSource=IIf(Not IsNull([Date_Payment_Received]),[Invoice_Value],0)

And this displays an appropriate value in each row.

I then added a textbox to the report footer with:-

Name=Paid_Value_Total
ControlSource=Sum([Paid_Value])

But no joy... I get the supply-a-value-prompt for Paid_Value when I run the report?.

Earlier I tried to add a Select SUM(zzzzz) AS into the query SQL but the syntax of that defeated me too....

Just when you've spent years getting your head around Access Forms... along pop Access Reports...

 

[vbaInet]

Sum() doesn't work on calculated fields. The field must exist in the report's record source. So move the following to the query the report is based on:

IIf(IsNull([Date_Payment_Received]), Null, [Invoice_Value])

Include this new field in your report and Sum() based on that field.

 

[gray]

Sorry if i'm being a bit thick here but this is where I was falling down yesterday... how do I place the IIF statement in my Query pls?

Using SQL view?

SELECT x,y,z, IIf(IsNull([Date_Payment_Received]), Null, [Invoice_Value]) AS Paid_Value FROM XXXXX WHERE blah blah

surely will fail.

Or in Query design view? :-

Field - "My_Paid_Value_Field"
Table - "Invoices"
Total - ?
Sort - ?
Criteria - ?
Or - ?

Never did understand that design gizmo... i tend to write in SQL itself.







With my query in SQL view?

 

[vbaInet]

In the query Design View, drop that IIF() text into a new column, move away from the column and it will create the Alias automatically for you. The alias will be called Expr1, change to something more meaningful.

 

[gray]

Well I never.... that's how it works!

Thanks chap... all sorted now... cheers....

P.S. just in case anyone else reads this....

1. edit your query
2. add statement into a new column in the "field" row e.g.

Paid_Value: IIf(Not IsNull([Date_Payment_Received]),[Invoice_Value],0)

Here, "Paid_Value" will be a new field in your report recordset
3. Add a new textbox in report footer and set controlsource=Sum([Paid_Value])

job done!

 

[vbaInet]

Happy to hear!