Solved 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.

 

[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.

 

[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

 

[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.