435,041 Members | 1,734 Online
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