Unable to derive a customizable expression

[Emmanuel Ayivi]

I thank you very much for your advise besides I don't mean to make things difficult for those Helping me or make them think too much. my appologies.

Now, I have a query named "Students subform" in an access database and in this query there is a table named "Student Subform Subquery" and it has the following fields; "StudentID", "Grade", "Grade(%)"... .

The expression below works correctly. However, i thought of making it customizable thus designing a form for it and make a command button in the event proceedure to fire this action on click but do not know how to go about it.

Grade: 
IIf([Grade (%)] Between 0.8 And 1,
    "Excellent",
    IIf([Grade (%)] Between 0.7 And 0.79,
        "Very Good",
        IIf([Grade (%)] Between 0.6 And 0.69,
            "Good",
            IIf([Grade (%)] Between 0.45 And 0.59,
                "Credit",
                IIf([Grade (%)] Between 0.35 And 0.44,
                    "Pass",
                    IIf([Grade (%)]<=0.34,
                    "Fail"))))))

I have a table named "ScoreRange" and in it i have the following fields; "ID", "Range From", "Range To", "Interpretation". So I want make the "Grade" expression to pick its information from the the table "ScoreRange".

Attached is a word copy of the screen print of what i mean.

Please help.

Thank you

[Seth Schrock]

However, I thought of making it customizable thus designing a form for it and make a command button in the event proceedure to fire this action on click but do not know how to go about it

I'm not sure what action you are talking about. Also, as I'm at work, I can't view your attached file.

A little side note, your query might be a little easier to read if you would use the Switch() function instead of nesting all those IIF statements. Switch Function

[zmbd]

Emmanuel Ayivi:
It just seems to be common practice to not download or open files that are not requested. You can directly up load the jpg the same way up uploaded the docx.

Seth's suggestion for the Select is also a very good one.

Now for your other question.
You should be able to refer to the second table from within the calculated field of the query much like a lookup field. However, instead of all of those between statments I'd use a single cut-off value.

[zmbd]

Sorry, called away for other project.

to continue my thought...

However, the logic behind the single cut off field might be a tad much

So If you use a table that has a min and max range for the grade lookup then in your query, design mode, show the table with your students grades and the grade lookup table. Pull down all of the fields from the table with the student data that you want to show (last_name, course, etc) and then pull down the field from the lookup table that has the user friendly text and set a criteria so that the student's grade is between the range.

Give it a try... if you're still haveing issues then post back.
You can then use a condition in the

[NeoPa]

Lose the [Range To] field, as it is redundant, and return the [Student Subform Subquery] record that matches the minimum value of [Range From] which is less than or equal to [Range (%)]. Then, your field would be coded as :

Range: [Interpretation]

[NeoPa]

I'd just like to add that this is a vast improvement over many recent questions that had to be deleted. This one, though not perfect, is certainly one of the better questions we get. Your effort is obvious and appreciated.