SUM on an expression

  • Thread starter Thread starter Geoff
  • Start date Start date
G

Geoff

Hi everyone,

I'm trying to get the subtotal of a load of numbers from a calculated
control on my continuous subform. I have read elsewhere that you can't sum a
calculated control but have to sum the expression. However, I get an error.
I've seen people say this works in other threads but it won't work for me.

This is the expression used for the control, that DOES work:

=IIf(parent.[points-dailytarget]-[daytotal]-[exercise]<=4,IIf(parent.[points
-dailytarget]-[daytotal]-[exercise]<0,0,parent.[points-dailytarget]-[daytota
l]-[exercise]),4)

So for the subtotal control in the footer I've put this:

=Sum(IIf(parent.[points-dailytarget]-[daytotal]-[exercise]<=4,IIf(parent.[po
ints-dailytarget]-[daytotal]-[exercise]<0,0,parent.[points-dailytarget]-[day
total]-[exercise]),4))

In case it makes a difference, the main form is based on a table and the
subform on a query, and the query has an expression in called 'daytotal'
that adds up other fields in the query.

Can anyone see a reason why this isn't working please?

Thanks very much,
Geoff.
 
Does any of the fields include a null value? What kind of error are you getting?
I would also get rid of the two nested IIFs and use the Format property
instead, replacing <0 items with 0.

Pavel
 
Pavel Romashkin said:
Does any of the fields include a null value? What kind of error are you getting?
I would also get rid of the two nested IIFs and use the Format property
instead, replacing <0 items with 0.

Pavel
Click to expand...


Thanks Pavel. I've actually sorted it out now though by putting the formula
in a query. I wasn't able to do that before because the formula referred to
the main form (well I assume that's why it didn't work) but now I've changed
the database around a bit and have got it working. Only one more problem to
go with this damn database now, and like the other problems I'll spend a few
hours researching it before resorting to posting here. :)

Geoff.
 
"Damn" database!? Don't let it get personal with you! After all, it is
just a bunch of 1s and 0s on a piece of ferrite-coated plastic that
*you* put there :-)

Pavel
 
Pavel Romashkin said:
"Damn" database!? Don't let it get personal with you! After all, it is
just a bunch of 1s and 0s on a piece of ferrite-coated plastic that
*you* put there :-)
Click to expand...

Ok, sorry Mr. Database, I won't be mean to you again. :)
 
Back
Top