Why does this not work? **Resolved**

[Matt_T_hat]

On the footer of a from set to "Continuous Forms" I have a text box with this code in it.

=IIf(IsNull(Sum([FinalPrice])),0,Sum([FinalPrice]))

FinalPrice is the name of a text box that contains another calculation. Why should this not work this time?

Last time I did this the form was backed by a table and was filtered. this time it's backed by a query. surely that's not the reason!?

the output is: #Error

[RobDog888]

This is an Access report? Your syntax is correct, but the
issue may be from the text box not yet being displayed so it is
neither null or 0. So by the IIF statement it will go to the false
scenerio. Then when it tries to display the contents there is
nothing to display so the formula get the #ERROR. You can not do
a SUM on all the instances of a text box. Can you use the field
from the query instead?

Can you post more information so we can be able to guide you in
the right direction?

[Ephesians]

Another possibility is this: If the [Final Price] does not contain a value in every record then the result will be #Error as Access cannot Sum a null. I would suggest that you add a further condition to your query controlsource for [Final Price]. Something along the lines of iif(isnull([Final Value],0,[Final Value]) so you don't have any empty fields being summed.

Just a thought.

[Matt_T_hat]

[Final value] is a calculated result and at present with some junk data in the DB no blank fields result.

From expirence nothing to add up results in nul returned and no maths is done.

Something along the lines of iif(isnull([Final Value],0,[Final Value]) so you don't have any empty fields being summed.

is a stunning idea but doesn't help much at the mo.

Still work is over for the day.

[Matt_T_hat]

The control source is now set to =sum([finalprice]) and still nothing.

It errors whatever I do.

For a time a second textbox set as defult = sum([finalprice]) gave the first row as an answer but that sits there blank.

I can find no deviation from when I've done it before.

FinalPrice is achieved via =([Unit Price]*[AmmountReq]) as the control

and the whole is backed by a query that has SQL:

SELECT Job.[Job ID], Example.Discriptor, Example.Size, Feature.Name, Feature.Measure, Material_Required.Qantity, _
[GET Cheapest Supplier].Qty, [GET Cheapest Supplier].Unit.Name, [GET Cheapest Supplier].[Unit Price], [GET Cheapest Supplier].[Unit ID], _
[GET Cheapest Supplier].Supplier.Name
FROM (Feature LEFT JOIN ([GET Cheapest Supplier] RIGHT JOIN Material_Required ON [GET Cheapest Supplier].[Unit ID] = _
Material_Required.[Unit ID]) ON Feature.[Feature ID] = Material_Required.[Feature ID]) RIGHT JOIN (Job LEFT JOIN Example ON Job.[Job ID] = Example.[Job ID]) _
 ON Feature.[Feature ID] = Example.[Feature ID]
WHERE (((Example.Disable)<>True))
WITH OWNERACCESS OPTION;

could my query be in the way somehow? I thought you could pritty much treat them like tables.

[MileOut]

3 Things

1. Your line: =IIf(IsNull(Sum([FinalPrice])),0,Sum([FinalPrice])) is a tad heavy on the functions

Wouldn't =Nz(Sum([FinalPrice])) do the same thing?

2. The SQLhas a possible spelling mistake - you refer to a field called Qantity, and not Quantity. This may however just be a typo.

3. If the worst comes to the worst with your Sum function then consider using the DSum() domain aggregate function.

[Matt_T_hat]

Sorry that was a bit rude of me, thanx for the help I don't remember how exactly but the customer is a happy one now.



BTW: Qantity was a badly named fieldname... lol

[RobDog888]

Originally posted by MileOut

1. Your line: =IIf(IsNull(Sum([FinalPrice])),0,Sum([FinalPrice])) is a tad heavy on the functions

Wouldn't =Nz(Sum([FinalPrice])) do the same thing?

This is not the same. From Ephesians's post will show the reason.
If the SUM([FinalPrice]) is equal to Null then the Access report will
show #Error on the report.

[Matt_T_hat]

Originally posted by RobDog888
This is not the same. From Ephesians's post will show the reason.
If the SUM([FinalPrice]) is equal to Null then the Access report will
show #Error on the report.

I see. Thankyou I was not fully aware of this.

[Granty]

SUM(Nz([FinalPrice], 0)) should work tho.

[Matt_T_hat]

Originally posted by Granty
SUM(Nz([FinalPrice], 0)) should work tho.

Good Use of default values and data integrity checks in the first place would be the best route.