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

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)))

Open in new window

0
dawber39
Asked:
dawber39
  • 9
  • 5
1 Solution
 
dawber39Author Commented:
Didn't have enough points first time around
0
 
mbizupCommented:
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
 
dawber39Author 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Jeffrey CoachmanCommented:
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
 
dawber39Author 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
 
mbizupCommented:
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
 
dawber39Author 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
 
dawber39Author Commented:
The two that work together - return #NUM! when the denominator turns out to be 0 - as I stated in my original post -
0
 
dawber39Author 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
 
mbizupCommented:
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
 
mbizupCommented:
Double-check the quotes ("0.00")...
Are you dealing with a text or numeric field?

If numeric then drop the quotes.
0
 
dawber39Author 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
 
dawber39Author Commented:
I appreciate you effort and sticking with it - thanks - you people up here rule
0
 
mbizupCommented:
Glad I could help out!:

<Thats it dude >

Just for the record, I'm a dudette.  :-)
0
 
dawber39Author Commented:
My sincere apologies - dudette

Take care
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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