• Status: Solved
• Priority: Medium
• Security: Public
• Views: 267

# MS Access Syntax

This code returns the percentage of males that are asian that have chosen a particular option, in this case option4 - however, when there arent any males in particular that have chosen option 4 (as is the case in the denominator - I get a #NUM! returned - here is the code:

=(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 have tried the below code - but I am not doing it correctly

=IIf(Sum(IIf([gender]="Male" And [Option4] Is Not Null And [Race]="Asian",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)))
``````=(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)))

=IIf(Sum(IIf([gender]="Male" And [Option4] Is Not Null And [Race]="Asian",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)))
``````
0
dawber39
• 9
• 5
1 Solution

Author Commented:
Didn't have enough points first time around
0

Commented:
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))))
0

Author Commented:
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
0

Commented:
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
0

Author Commented:
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
0

Commented:
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.
0

Author Commented:
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)))
0

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

Author Commented:
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
0

Commented:
This compiles:
= 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))))
0

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

If numeric then drop the quotes.
0

Author Commented:
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))))
0

Author Commented:
I appreciate you effort and sticking with it - thanks - you people up here rule
0

Commented:

<Thats it dude >

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

Author Commented:
My sincere apologies - dudette

Take care
0

## Featured Post

• 9
• 5
Tackle projects and never again get stuck behind a technical roadblock.