MS Access Syntax

Didn't have enough points first time around

Were you getting incorrect results or a syntactical error?

I think you may have been missing a ")" .

Try this:
 = IIf((Sum(IIf([gender] = "Male" And [Option4] Is Not Null, 1, 0))) = 0, 0, (Sum(IIf([gender] = "Male" And [Option4] Is Not Null And [Race] = "Asian", 1, 0))) / (Sum(IIf([gender] = "Male" And [Option4] Is Not Null, 1, 0))))

When I use mine it says the expression you entered has a function that contains the wrong number of arguments - and the solution you provided states that it has an invalid syntax

dawber39,

Always strive to keep things simple at first, then build up.
(It is a lot easier to troubleshoot a small formula than a big one.)

So,...
Separate out all the IIF formulas and test them on thier own.
If one of them fails, then fix it.
Then one by one, as they work, you can begin nesting them.
After you add each formula, test it.  If it does not work then fix it.

This way you are stringing together *working* formulas.

With one Huge formula, you really have no idea what parts may not be working.

So, ...first break out all the IIF formals and test them individually.
And get them all working independently.

Then report back here and one of us can help you with the nesting.

Make sense?

;-)

JeffCoachman

Yes it makes sense - but I have other nested formulas - that are working fine, and time is running short for me - like this one returns a percentage of those who chose option one but also chose option 3

=IIf(Sum(IIf([gender]="Female" And [Option1] Is Not Null And [Race]="Asian",1,0))=0,"0.00",(Sum(IIf([gender]="Female" And [Option1] Is Not Null And [Race]="Asian" And [Option3] Is Not Null,1,0)))/(Sum(IIf([gender]="Female" And [Option1] Is Not Null And [Race]="Asian",1,0))))

I do not think I am going to have the time to break it down - I was hoping for a quick fix - Thanks for trying

I'd actually recommend rewriting the whole thing as a VBA function, and call that function from your query or control source

This would
- Make your code easier to maintain
- Simplify the statement in your controlsource or query.

There appears to be something wrong in here: (Wrong number of arguments)

=IIf(Sum(IIf([gender]="Male" And [Option4] Is Not Null And [Race]="Asian",1,0)))=0,"0.00"

What comes after that works fine - this
(Sum(IIf([gender]="Male" And [Option4] Is Not Null,1,0)))

and this

/(Sum(IIf([gender]="Male" And [Option4] Is Not Null,1,0)))

These two work together
=(Sum(IIf([gender]="Male" And [Option4] Is Not Null And [Race]="Asian",1,0)))/(Sum(IIf([gender]="Male" And [Option4] Is Not Null,1,0)))

The two that work together - return #NUM! when the denominator turns out to be 0 - as I stated in my original post -

I have over 300 calculations in this report - I cannot possibly rewrite everything into VBA at this point - is DatabseMX around? He was working on this with me before - and he solved some problems for me

Double-check the quotes ("0.00")...
Are you dealing with a text or numeric field?

If numeric then drop the quotes.

Thats it dude - you did it - and saved me some time - thank you

= IIf((Sum(IIf([gender] = "Male" And [Option4] Is Not Null, 1, 0))) = 0, "0.00", (Sum(IIf([gender] = "Male" And [Option4] Is Not Null And [Race] = "Asian", 1, 0))) / (Sum(IIf([gender] = "Male" And [Option4] Is Not Null, 1, 0))))

I appreciate you effort and sticking with it - thanks - you people up here rule

Glad I could help out!:

<Thats it dude >

Just for the record, I'm a dudette.  :-)

My sincere apologies - dudette

Take care