How to Customize an IF Function in vba using a form

[sashasandy]

hallo experts!
i would like to customize an if function in vba,
that is allow the user to change the condition, value if true and value if false all in a form.
how would i achieve this please!!!

Grade: IIf([Marks]>=80, "A", _
   IIf([Marks]>=75,"A-", _
      IIf([Marks]>=70,"B+", _
         IIf([Marks]>=65,"B", _
            IIf([Marks]>=60,"B-", _
               IIf([Marks]>=55,"C+", _
                  IIf([Marks]>=50,"C", _
                     IIf([Marks]>=45,"C-", _
                        IIf([Marks]>=40,"D+", _
                           IIf([Marks]>=35,"D", _
                              IIf([Marks]>=30,"D-", _
                                 IIf([Marks]<30,"E")
)))))))))))

[Seth Schrock]

Are you using VBA or SQL? Your question says VBA, but your code is SQL.

If VBA, then I would recommend setting up a table that would hold a high value and a low value plus the value if true. For example

HighV   LowV   Grade
80      76     A
75      71     A-
70      66     B+

Then when you enter in a grade value, you just query this table for the record where HighV >= Given Value AND LowV <= Given Value. You can then base a form on this table so that you can change your values and grades.

Just to let you know, your code as written wouldn't work like you expect. As soon as the code gets returned as true, then it doesn't go through the rest of the If statements. So say your grade was 40. Since 40 is less than or equal to 80, it would give you a grade of A and then skip the rest of the ifs. You would have to reverse the order of the numbers (low grades first and high grades last) to be able to do it your way.

[zmbd]

sashasandy:
Seth is correct. What you have is an SQL version of this situation.

I personally hate nested IIF(). I would consider using the Switch Function if you going to do this as a calculated field. Becarefull here as the function is evaluated from left to right. The first true statement from left to right will be returned so your logic will have to reflect this.

In VBA would be considering either looking at the Select...Case Statement
OR
If using Seth's method the table and DLookup() Function
The choice would be based upon design. With the table approach, if the grade ranges change, then there is no need to re-code the application and would be the approach I would use if building a grade-book for a teacher.


@Seth:

Just to let you know, your code as written wouldn't work like you expect. As soon as the code gets returned as true, then it doesn't go through the rest of the If statements.

This is not a correct statement. IIF() functions evaluate all of the IIF() conditionals. So in this case, as written by OP, an 85 will give you an A, 64=B-, and so forth. Try it as a calculated field, remember to remove the underscores that I used to step the code in OP.

[Seth Schrock]

Oops. As evidenced by my wording I mis-read it to say less than or equal too when it is actually greater than or equal to. So the code would work in the order written.

I am really confused by the fact that the IIF() function evaluates both the true and the false parts as a grade of 90 would return true for each of the conditions so how does it decide to give it an A instead of a D since it evaluated if 90>=35. I really wish I could step through the code like you can in VBA to see that work as that doesn't make sense that any language would use that functionality.

I just googled stepping through SQL code and evidently you can in SQL Server so I'm going to give that a try.

[zmbd]

Seth, because as written, it returns the first true condition in the branch. So for 90 (>=80) == "A", even though it evaluated the remaining, only the first level is the most correct. For 56 (>=55) nothing until this level is true and it is the first level returning true, thus the "C+"

It would make more sense if you charted it. :)

[sashasandy]

Seth! Am so sorry i dint see this thread, i thought no one bothered.But thank you very much....am going to try your method right away.. although i posted it here also: http://goo.gl/spthFy.
I also got a solution using a complex subquery. quite similar with your idea of making a table first.
Well, The challenge with the method i found is: I can only compare one value at a time. that is in this case "Marks". I would also like to get grades for individual Subjects marks.Note: the "Marks" used in this query is the total of all the individual subject scores.

SQL.Satement:

 SELECT SM.Student_ID
   , SM.Students
   , SM.Marks
   , GD.Grade
FROM tblStudentMarks 
   AS SM 
      INNER JOIN tblGradeDenormalized 
         AS GD 
       ON (SM.Marks>=GD.MinMarks) 
            AND (SM.Marks<=GD.MaxMarks);

ZMBD:-) Thanks for leading me here from the other thread, Actually i think this is the best Method if i get it to work as i want completely.

[zmbd]

sashasandy:
Glad this is working for you; however, I'm still not quite sure about your underlying database structure.

Well, The challenge with the method i found is: I can only compare one value at a time. that is in this case "Marks". I would also like to get grades for individual Subjects marks.

Mad Hatter: Clean cup, clean cup. Move down. You'll need a new thread.
If your database is properly normalized you will have no problems with this (^.^)


Alice's Adventures in Wonderland one of my favorite books by LC, would love to find the Screwtape Letters one of these days.