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!
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!
Comment