By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,734 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

Expression with multiple if/then variables

P: 2
I am working with MS Access 2007, using Windows XP.

I have eights fields in one section of my form. Each one has a combo box
with the following 3 options:

Field1: 2.5, 0, "" (I am using a blank to designate a null value)
Field 2: 2.5, 0, ""
Field 3: 2.5, 0, ""
Field 4: 2.5, 0, ""
Field 5: 10, 0, ""
Field 6: 5, 0, ""
Field 7: 5, 0, ""
Field 8: 5, 0, ""

If a numerical value is chosen, that indicates the task was completed, 0
indicates that it was not, and "null" means that it was not applicable.

(Numerical values in this section sum up to 35)

In essence, each field is weighted differently. Now there are a number of
variations that can happen.

For example, let's say that fields 1 - 7 are applicable & field 8 is not,
BUT field 5 (a value of 10) was not completed, so it was given a "0" zero
value. This changes the total to 20 (adding fields 1,2,3,4,6,7) out of 30,
because field 8 was not applicable and thus given a null value. To get the
percentage, 20/30 = 66.6%.

Yet another example: only fields 7 & 8 were applicable and both were
completed for a total of 10, while fields 1 - 6 were given null values, as they did not apply. I would then need the expression to divide the
10 by 10 (the total value) which then gives me 100%.

Right now I have this in my control source for the control that is calculating the percentage:
=(Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)+Nz([Field4],0)+Nz([Field5],0)+Nz([Field6],0)+Nz([Field7],0)+Nz([Field8],0))/35

This obviously only works if they're all selected, as it divides by 35.

My question is how do I get the expression to account for any & all of these
variables?

Should I be looking into the "IIF" function?

HELP!
Mar 14 '07 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,366
You can use the iif function to build the denominator:
Expand|Select|Wrap|Line Numbers
  1. iif(Field1 Is Null, 0, 2.5) + iif(Field2 Is Null, 0, 2.5) ...
And so on.
Mar 14 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.