How to compare "varchar" values?

[Tim]

I need to compare a student's score (that s/he got it after a test) with the
score requirement. The problem is the scores are either a "character" only,
i.e., "2"
OR combined a "character" AND the '+'/ '-' character, i.e., 2+.

For example, if the require score is 2+ and the student's score is 2 then
that student is not qualified. The datatype of scores is varchar. Can you
please help in programming how to compare these values? Thanks a lot in
advance. I appreciate it.

 

[vanderghast]

I see two approaches. The first one is a trick, really:

eval( replace(replace(score, "+", "+0.25"), "-", "-0.25")


which changes "2+" to 2.25, "2-" to 1.75, and "2" into 2, so that "1"
, "1+", "2-", "2", "2+ is sorted ascending.


The second solution, more database like, is to define a table of scores, two
fields, score and scoreValue:

score scoresValue
"2-" 1.75
"2" 2
"2+" 2.25
...
"Good" 8
"Excellent" 10
....


ie, it allows you to translate the score into a numerical value and then,
you have to compare the score value instead of its 'name' (using inner
joins/ DLookup).



Vanderghast, Access MVP

 

[Tim]

Thank you! You guys are smart!

I see two approaches. The first one is a trick, really:

eval( replace(replace(score, "+", "+0.25"), "-", "-0.25")


which changes "2+" to 2.25, "2-" to 1.75, and "2" into 2, so that "1"
, "1+", "2-", "2", "2+ is sorted ascending.


The second solution, more database like, is to define a table of scores, two
fields, score and scoreValue:

score scoresValue
"2-" 1.75
"2" 2
"2+" 2.25
...
"Good" 8
"Excellent" 10
....


ie, it allows you to translate the score into a numerical value and then,
you have to compare the score value instead of its 'name' (using inner
joins/ DLookup).



Vanderghast, Access MVP




.