Microsoft Access SUM #Error

[VarunJain]

I have an access form and subform that i created using a query. Then, through design view, I created a text box, and entered a rather long nested iif that referred to other fields in the subform.

Now, I want to use the SUM function to add up the values (results of iff) in the created text boxes, each which have different values. I have tried everything I can think of, but it always comes up with #Error! no matter what I do.

Can anyone help me with this?

[alansidman]

Are the results of your IIF statement actually numbers or are they text that look like numbers, or are any of the text box results of the IIF statement actually null or empty?

If the former, then you need to change the results to numbers, and if the latter, then you may need to employ the NZ function into your calculations.

[Missinglinq]

Also, you cannot use an Aggregate Function against a calculated Field, which I think is what you have using your IIFs...you have to use them directly against the calculated expression itself. I won't attempt that, using nested IIFs (which are frequently troublesome,) but here's a simple example of the concept:

If

C = A + B

you cannot use

SUM(C)

but have to use

SUM(A + B)

But given how problematic nested IIFs can be, in addition to the questions posed by alansidman, you need to make sure that you're actually getting the results from the IIFs that you expect.

Linq ;0)>

[davegri]

if you're using something like:
dim mytotal as long
mytotal = sum(t1 +t2 +t3)

That won't work. Sum is not a vba function. You can use it in sql but not in vba.

The above should simply be
mytotal = t1 + t2 + t3

[Micron]

Once you figure out if there are text values or nulls involved (and deal with them if there are) you might want to use the DSum function over the query or table records that the form contains. Look here for info on using that function (see Domain Aggregate Functions). You'd put the function in your calculated control - unless you're stuck with a bunch of messy IIF's.
https://www.techonthenet.com/access/functions/

[Missinglinq]

Apparently not a pressing problem...since we haven't heard from the OP in 10 days!

[alansidman]

Apparently not a pressing problem...since we haven't heard from the OP in 10 days!

or one of our answers triggered a solution and the OP does not care nor wish to acknowledge.

[Missinglinq]

Be nice to know, wouldn't it?

BTW, love your avatar!

Linq ;0)>