Microsoft Office access 2007 (upgrade, p...

I am trying to grade the students and i keep getting

"F" with this formulaIIF( [Grade (%)] BETWEEN 0 AND 35,'F',IIF( [Grade (%)] BETWEEN 36 AND 55, 'D',IIF( [Grade (%)] BETWEEN 56 AND 70,'C', IIF( [Grade (%)] BETWEEN 71 AND 90,'B','A'))))

Please help

It has been a very long time since I did anything with Access and you might want to ask your question in the Programming or Database forums. But you might try this and see if it actually works:

IIF( [Grade (%)] <= 35,'F',IIF( [Grade (%)] <= 55, 'D',IIF( [Grade (%)] <= 70,'C', IIF( [Grade (%)] <=90,'B','A'))))

MIKE

Hi, If you are doing this in Access, your problem may be that you are testing an underlying value 0 to 1, rather than 0 to 100. Try changing your values to 0, 0.35, 0.36 etc.

In Excel you could do this:

If you are trying to get a Grade for a value, you can do this with the VLOOKUP() function.Enter this into cells D1 to E5:

D E 1 0 F 2 36 D 3 56 C 4 71 B 5 90 AIf there is a score as a number (0 to 100) in cell A1, put this formula in cell B1:

=VLOOKUP(B1,$D$1:$E$5,2)If the value in B1 is a percentage, i.e., a value from 0 to 1 formatted as a percentage, change the values in column D to

0, 0.36 etc, then format them as percentages.Regards

Humar, I believe the OP is using Access, not Excel, but you went to same place I was going.

I don't do Access, but could it be an "order of magnitude" problem?

.26 if between 0 and 35, therefore a similar Excel IF statement would always find the first condition to be TRUE and return an "F".

Maybe vinodk1 should be using:

[Grade (%)] BETWEEN 0 AND .35

Note the addition of the decimal point.

Hi DerbyDad03, I realized my error from Mike's reply, so changed my response.

Regards

DerbyDad03:

Never thought of the"order of magnitude"So the formula should be something like:

IIF([Grade (%)] <= .35,'F',IIF([Grade (%)] <= .55, 'D',IIF([Grade

(%)] <= .70,'C', IIF([Grade (%)] <=.90,'B','A'))))MIKE

Hi,

Thanks for getting back to me. I found out the table was holding the values in 0.00 format as mentioned above, I was able to modify the query and run it successfully. I changed it to the following asIIF( [Grade (%)] BETWEEN 0 AND 0.55,'F',

IIF( [Grade (%)] BETWEEN 0.56 AND 0.6299, 'D-',

IIF( [Grade (%)] BETWEEN 0.63 AND 0.6599, 'D',

IIF( [Grade (%)] BETWEEN 0.66 AND 0.6899, 'D+',

IIF( [Grade (%)] BETWEEN 0.69 AND 0.7299, 'C-',

IIF( [Grade (%)] BETWEEN 0.73 AND 0.7599, 'C',

IIF( [Grade (%)] BETWEEN 0.76 AND 0.7899, 'C+',

IIF( [Grade (%)] BETWEEN 0.79 AND 0.8299,'B-',

IIF( [Grade (%)] BETWEEN 0.83 AND 0.8599, 'B',

IIF( [Grade (%)] BETWEEN 0.86 AND 0.8899, 'B+',

IIF( [Grade (%)] BETWEEN 0.89 AND 0.9299, 'A-',

IIF( [Grade (%)] BETWEEN 0.93 AND 0.9599, 'A',

IIF( [Grade (%)] BETWEEN 0.96 AND 0.9899,'A+','A++')))))))))))))Thanks for replying back. Appreciate all your help.

Ask Your Question

Weekly Poll