Sum If

[dullster]

I'm trying to do a sum if. If Client Title is "Chairman", sum the $Medicare. I have it as =Sum(IIf([Client Title],"Chairman",[$Medicare])) and I'm getting, This expression is typed incorrectly or it is too complex to be evaluated.

 

[June7]

=Sum(IIf([Client Title]="Chairman", [$Medicare], 0))

Are there other conditional Sum expressions?

Strongly advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

 

[plog]

Here's good documentation on IIf:

MS Access: iif Function

This MSAccess tutorial explains how to use the Access iif function with syntax and examples. The Microsoft Access iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

www.techonthenet.com

IIf(test, true result, false result)

Your IIf doesn't have a complete test, it's just one field. You need a comparison operator (=, <,>, etc) and what to test [Client Title] against

Also, your doing yourself no favors with field names using spaces in them.

 

[Pat Hartman]

If you're thinking of doing x queries, one for each value in the field, don't. Use a totals query if you can live with the results returned as one per row. Or use a crosstab if you want one row returned with x columns.

 

[Mark_]

Were this me, I would probably use a sum query on $Medicare and use a control to hold the title. This lets you use the same structure for any title.

 

[Pat Hartman]

Were this me, I would probably use a sum query on $Medicare and use a control to hold the title. This lets you use the same structure for any title.

That is the least efficient method. PLUS, it GUARANTEES program maintenance will be required if some value in that column changes. The list method displayed in subform is the least likely method to require future maintenance. Even the Crosstab is likely to require form maintenance if the number of items increases and makes the crosstab too wide.

 

[Mark_]

That is the least efficient method. PLUS, it GUARANTEES program maintenance will be required if some value in that column changes. The list method displayed in subform is the least likely method to require future maintenance. Even the Crosstab is likely to require form maintenance if the number of items increases and makes the crosstab too wide.

OP posted in the REPORT forum, so I'm not looking at subforms or queries.
OP hasn't shown where in the report this is being used.

OP simply has "Chairman" as a criteria and wants a sum. One call to a query or DSUM() would do this with little effort on OPs part if done only ONCE. Bonus if the query is done server side.

Will agree, as the OP is using a hard coded value, ANY change to table values will guarantee they need to go in and change the hard coded value. Implication is OP is using either a lookup by value instead of by reference OR they are allowing end users to type in the value.

 

[CJ_London]

or it is too complex to be evaluated.

This error implies the sum is in a query, you would just get #error if it was the controlsource to a control

 

[Pat Hartman]

OP posted in the REPORT forum, so I'm not looking at subforms or queries.

Reports are easily bound to Crosstabs.

Any method that requires that the programmer know a list of values ahead of time and create separate counts for hardcoded values is a bad method and should be discouraged. It isn't a matter of whether there are 5 variations of potential values or 5,000. If you wouldn't hardcode the solution for 5,000 values, you shouldn't hard code it for 5.

 

[June7]

I tested this bad expression in a textbox on report and get "Data type mismatch in criteria expression." and report won't even open.

If expression is in a query, why is it preceded with = sign?

 

[CJ_London]

I didn’t isay it was, just that is what the error implies. Why assume a report?

OP has not provided relevant information as to where they are using this calc which will impact the solution

It is also possible the use of $ in a field name, even with square brackets, could be the issue. I started a thread years ago where OP’s reported errors with misleading descriptions- solution was field had non alpha numeric characters and the code worked once the field was renamed

 

[LarryE]

I'm trying to do a sum if. If Client Title is "Chairman", sum the $Medicare. I have it as =Sum(IIf([Client Title],"Chairman",[$Medicare])) and I'm getting, This expression is typed incorrectly or it is too complex to be evaluated.

First, get rid of the $ in the $Medicare field name. Never ever use special characters in a table or field name.

 

[dullster]

=Sum(IIf([Client Title]="Chairman", [$Medicare], 0))

Are there other conditional Sum expressions?

Strongly advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

That was it. Thank you. I have it working. I thought I tried that but must not have.

 

[Pat Hartman]

Why assume a report?

Because the question was posted in reports