Building expressions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that is built off of a table. The table has two fields - an
ID number and a premium. In the report I have two expressions that do not
calculate the way I had hoped. The first one is:

="There were " & (IIf([premium]>50,Count([premium]),0)) & " participants pay
more than $50/month."

The expression should return a value of 4, but returns a value of 0.

The second expression is:

="There were " & Count(Min([premium])) & " participants pay the minimum ($"
& Min([premium]) & ")."

I get an error that you can not have an aggregate function in
expression(Count(Min[premium]). If I remove "Min" from the expression, it
returns a value greater than the number of records in the database.


I understand that reports run better off of queries, but I did not build
this database and at this point it seems easier to fix the two expressions
than to rebuild the report off a query and have to fix many expressions.

Please help
 
I have a report that is built off of a table. The table has two fields - an
ID number and a premium. In the report I have two expressions that do not
calculate the way I had hoped. The first one is:

="There were " & (IIf([premium]>50,Count([premium]),0)) & " participants pay
more than $50/month."

The expression should return a value of 4, but returns a value of 0.

The second expression is:

="There were " & Count(Min([premium])) & " participants pay the minimum ($"
& Min([premium]) & ")."

I get an error that you can not have an aggregate function in
expression(Count(Min[premium]). If I remove "Min" from the expression, it
returns a value greater than the number of records in the database.


I understand that reports run better off of queries, but I did not build
this database and at this point it seems easier to fix the two expressions
than to rebuild the report off a query and have to fix many expressions.
Click to expand...

I think the problem may be from a misunderstanding of how the Count
operator works: it doesn't count values, it counts RECORDS. If you
want to count the number of records *for which the premium is greater
than 50*, try

= "There were " & Sum(IIF([Premium] > 50, 1, 0)) & " participants...


John W. Vinson[MVP]
 
Sorry for the delayed response, I had to go out of town right after I posted
this question. Thank you very much for your response. I will give your
expression a try.

John Vinson said:
I have a report that is built off of a table. The table has two fields - an
ID number and a premium. In the report I have two expressions that do not
calculate the way I had hoped. The first one is:

="There were " & (IIf([premium]>50,Count([premium]),0)) & " participants pay
more than $50/month."

The expression should return a value of 4, but returns a value of 0.

The second expression is:

="There were " & Count(Min([premium])) & " participants pay the minimum ($"
& Min([premium]) & ")."

I get an error that you can not have an aggregate function in
expression(Count(Min[premium]). If I remove "Min" from the expression, it
returns a value greater than the number of records in the database.


I understand that reports run better off of queries, but I did not build
this database and at this point it seems easier to fix the two expressions
than to rebuild the report off a query and have to fix many expressions.
Click to expand...

I think the problem may be from a misunderstanding of how the Count
operator works: it doesn't count values, it counts RECORDS. If you
want to count the number of records *for which the premium is greater
than 50*, try

= "There were " & Sum(IIF([Premium] > 50, 1, 0)) & " participants...


John W. Vinson[MVP]
Click to expand...
 
Back
Top