Grade Conversion

[Aman]

Hello everyone,

I am working on a simple report card database for a school. Everything works correctly, except for converting number grades to letter grades.

They want the ability to input number grades or letter grades into the database, but print only letter grades. No problem, just have the fields set as text fields and write a custom function that will convert any numerical value to it's corresponding letter.

For some reason, my custom function is returning incorrect values for numbers greater than 99. It appears to only be reading the first 2 digits. For example, it will return an F for 100 and will return an A- for 900. Granted the 900 figure is just for testing, but with extra credit numbers in the low 100's are quite possible.

If the field I'm pulling from is set as a Number field, then the results are correct, but I don't see any way to do that since they need the ability input both letters and numbers.

Here's my custom function:

If(

    GetAsNumber( class ) = "";

    class;

    Case(

        class ≥ 96;

        "A";

        class ≥ 90;

        "A-";

        class ≥ 88;

        "B+";

        class ≥ 85;

        "B";

        class ≥ 80;

        "B-";

        class ≥ 78;

        "C+";

        class ≥ 75;

        "C";

        class ≥ 70;

        "C-";

        class ≥ 65;

        "D";

        class ≥ 0;

        "F"

    )

)

Any help will be greatly appreciated.

Thank you,

Aman

Edited February 14, 2008 by Guest

[Colin Keefe]

You need to convert "class" to number across the board. Try something like:

Let([



@class = GetAsNumber(class)



];



If (@class= "" ; class ; 



Case (

@class="";@class;

@class ≥ 96; "A";

@class ≥ 85; "B";

@class ≥ 80; "B-";

@class ≥ 78; "C+";

@class ≥ 75; "C"; 

@class ≥ 70; "C-";

@class ≥ 65; "D";

@class  ≥ 0; "F")



)



)

[comment]

Text comparisons are not the same as number comparisons. For example, "100" < "99" returns true, because the word "100" comes before "99" in alphabetical order. Try it this way:

Let (

n = GetAsNumber ( class )

;

Case (

IsEmpty ( n ) ; class ;

n ≥ 96 ; "A" ;

n ≥ 90 ; "A-" ;

n ≥ 88 ; "B+" ;

n ≥ 85 ; "B" ;

n ≥ 80 ; "B-" ;

n ≥ 78 ; "C+" ;

n ≥ 75 ; "C" ;

n ≥ 70 ; "C-" ;

n ≥ 65 ; "D" ;

n ≥ 0 ; "F"

)

)

[Aman]

Thanks guys!!! Both ways seem to work nicely...now which to choose. :

[Colin Keefe]

Eerie, isn't it? :

Michael's is a little cleaner, my [ ] brackets are superfluous. And isempty() is just nicer than blah="".

[LaRetta]

For example, "100" < "99" returns true, because the word "100" comes before "99" in alphabetical order.

To expand just a bit ... 1 comes before 9 which is what alpha sort always does does to numbers (it begins at the left character, sorts, then moves to the right instead of seeing an entire 'number.' :wink2:

[comment]

To correct just a bit... a text sort deals with digits (or numeric characters) - not numbers.