Totalling expressions

[Sue Compelling]

Hi

I have a report that is grouped by Programme and then grouped by Trust.

I have 2 expressions (side by side) in the detail section, as I want these
figures displayed beside each other

=IIf([Year]="2005",[Started],Null) - started being the number of
students
=IIf([Year]="2006",[Started],Null) - started being the number of
students

Now I want to sub-total these expressions in the Trust footer. I've tried
my entire range of altenatives but can't get these figures.

TIA - any help much appreciated

 

[Allen Browne]

Sue, it might be possible to use an expression like this:
=Sum(IIf([Year]="2005",[Started],Null))

But there's a couple of things that could go wrong here:

a) If [Year] is a Number type field (not a Text type field), drop the
quotes:
=Sum(IIf([Year] = 2005, [Started], Null))

b) The name Year is a reserved word in JET SQL, and a function name in VBA,
so there's a fairly good chance that it will be misunderstood.

It might help to rename the field to something else, *after* turning off the
Name AutoCorrect feature that would prevent Access making the change
properly:
http://allenbrowne.com/bug-03.html

Another alternative would be use a query as the source for the report, and
place the calculated expression there:
Year2005: CLng(Nz(IIf([Table1].[Year] = 2005, [Started], 0),0))
That expression should get around most of the possible problems (reserved
name, numeric matching, nulls, and typecasting the result. More on the need
to typecast:
http://allenbrowne.com/ser-45.html

HTH

 

[Sue Compelling]

Thanks Allen - brilliant.
--
Sue Compelling

Sue, it might be possible to use an expression like this:
=Sum(IIf([Year]="2005",[Started],Null))

But there's a couple of things that could go wrong here:

a) If [Year] is a Number type field (not a Text type field), drop the
quotes:
=Sum(IIf([Year] = 2005, [Started], Null))

b) The name Year is a reserved word in JET SQL, and a function name in VBA,
so there's a fairly good chance that it will be misunderstood.

It might help to rename the field to something else, *after* turning off the
Name AutoCorrect feature that would prevent Access making the change
properly:
http://allenbrowne.com/bug-03.html

Another alternative would be use a query as the source for the report, and
place the calculated expression there:
Year2005: CLng(Nz(IIf([Table1].[Year] = 2005, [Started], 0),0))
That expression should get around most of the possible problems (reserved
name, numeric matching, nulls, and typecasting the result. More on the need
to typecast:
http://allenbrowne.com/ser-45.html

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi

I have a report that is grouped by Programme and then grouped by Trust.

I have 2 expressions (side by side) in the detail section, as I want these
figures displayed beside each other

=IIf([Year]="2005",[Started],Null) - started being the number of
students
=IIf([Year]="2006",[Started],Null) - started being the number of
students

Now I want to sub-total these expressions in the Trust footer. I've tried
my entire range of altenatives but can't get these figures.

TIA - any help much appreciated