Convert Number to Text!

[Alex]

Dear All,

I have a Query in which you can find a columns with numbers, from 30 to 100,
I want these numbers to be converted in to letters (A, B, C, D, E, F)
according to this,

100 : 95 => A*
94 : 85 ==> A
84 : 75 ==> B
74 : 65 ==> C
64 : 55 ==> D
54 : 50 ==> E
49 : 30 ==> F
0 ==> N/A

The letters should appear on a report.

I tried to use (http://www.mvps.org/access/modules/mdl0001.htm) but it was a
little bit difficult to know all the combination of numbers especially that
I might have some fraction 84.3 so maybe someone can help me with it.

Thanks
Alex

 

[Andi Mayer]

Dear All,

I have a Query in which you can find a columns with numbers, from 30 to 100,
I want these numbers to be converted in to letters (A, B, C, D, E, F)
according to this,

100 : 95 => A*
94 : 85 ==> A
84 : 75 ==> B
74 : 65 ==> C
64 : 55 ==> D
54 : 50 ==> E
49 : 30 ==> F
0 ==> N/A

The letters should appear on a report.

attention: aircode!!!!!
Function makeAlpha(Value as Variant) as string
if Value >= 95 then
makeAlpha="A*"
elseif Value >=85 then
makeAlpha="A*"
........
else
makeAlpha="N/A"
endif
end sub

 

[Dirk Goldgar]

Dear All,

I have a Query in which you can find a columns with numbers, from 30
to 100, I want these numbers to be converted in to letters (A, B, C,
D, E, F) according to this,

100 : 95 => A*
94 : 85 ==> A
84 : 75 ==> B
74 : 65 ==> C
64 : 55 ==> D
54 : 50 ==> E
49 : 30 ==> F
0 ==> N/A

The letters should appear on a report.

I tried to use (http://www.mvps.org/access/modules/mdl0001.htm) but
it was a little bit difficult to know all the combination of numbers
especially that I might have some fraction 84.3 so maybe someone can
help me with it.

Thanks
Alex

Suppose you have a table named GradeMappings like with fields like
these:

GradeMappings
-----------------
LowGrade (Number/Integer)
HighGrade (Number/Integer)
GradeLetter (Text)

The table would then have records like these:

95, 100, "A*"
85, 94, "A"
...
0, 29, "N/A"

I'm not sure about that last entry, because you don't say what should
happen if -- or if it's possible that -- the number grade is in the
range 1-29.

You could use a DLookup expression in a text box:

=DLookup("GradeLetter", "GradeMappings",
[NumberGrade] & " Between LowGrade And HighGrade")

Or you could build the lookup into your report's query with an imprecise
join:

SELECT Grades.<some fields>, GradeMappings.GradeLetter
FROM Grades
LEFT JOIN GradeMappings
ON Grades.NumberGrade
Between GradeMappings.LowGrade
And GradeMappings.HighGrade;

That's "air SQL", but something along those lines ought to work. Note
that you won't be able to display a query with that SQL in design
view -- you have to work in SQL view.

 

[Alex]

THANX it worked

I dind't know how to use the DLookup now I know, thanks to you,

Alex

Dear All,

I have a Query in which you can find a columns with numbers, from 30
to 100, I want these numbers to be converted in to letters (A, B, C,
D, E, F) according to this,

100 : 95 => A*
94 : 85 ==> A
84 : 75 ==> B
74 : 65 ==> C
64 : 55 ==> D
54 : 50 ==> E
49 : 30 ==> F
0 ==> N/A

The letters should appear on a report.

I tried to use (http://www.mvps.org/access/modules/mdl0001.htm) but
it was a little bit difficult to know all the combination of numbers
especially that I might have some fraction 84.3 so maybe someone can
help me with it.

Thanks
Alex

Suppose you have a table named GradeMappings like with fields like
these:

GradeMappings
-----------------
LowGrade (Number/Integer)
HighGrade (Number/Integer)
GradeLetter (Text)

The table would then have records like these:

95, 100, "A*"
85, 94, "A"
...
0, 29, "N/A"

I'm not sure about that last entry, because you don't say what should
happen if -- or if it's possible that -- the number grade is in the
range 1-29.

You could use a DLookup expression in a text box:

=DLookup("GradeLetter", "GradeMappings",
[NumberGrade] & " Between LowGrade And HighGrade")

Or you could build the lookup into your report's query with an imprecise
join:

SELECT Grades.<some fields>, GradeMappings.GradeLetter
FROM Grades
LEFT JOIN GradeMappings
ON Grades.NumberGrade
Between GradeMappings.LowGrade
And GradeMappings.HighGrade;

That's "air SQL", but something along those lines ought to work. Note
that you won't be able to display a query with that SQL in design
view -- you have to work in SQL view.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Douglas J. Steele]

Dear All,

I have a Query in which you can find a columns with numbers, from 30
to 100, I want these numbers to be converted in to letters (A, B, C,
D, E, F) according to this,

100 : 95 => A*
94 : 85 ==> A
84 : 75 ==> B
74 : 65 ==> C
64 : 55 ==> D
54 : 50 ==> E
49 : 30 ==> F
0 ==> N/A

The letters should appear on a report.

I tried to use (http://www.mvps.org/access/modules/mdl0001.htm) but
it was a little bit difficult to know all the combination of numbers
especially that I might have some fraction 84.3 so maybe someone can
help me with it.

Thanks
Alex

Suppose you have a table named GradeMappings like with fields like
these:

GradeMappings
-----------------
LowGrade (Number/Integer)
HighGrade (Number/Integer)
GradeLetter (Text)

The table would then have records like these:

95, 100, "A*"
85, 94, "A"
...
0, 29, "N/A"

I'm not sure about that last entry, because you don't say what should
happen if -- or if it's possible that -- the number grade is in the
range 1-29.

You could use a DLookup expression in a text box:

=DLookup("GradeLetter", "GradeMappings",
[NumberGrade] & " Between LowGrade And HighGrade")

Or you could build the lookup into your report's query with an imprecise
join:

SELECT Grades.<some fields>, GradeMappings.GradeLetter
FROM Grades
LEFT JOIN GradeMappings
ON Grades.NumberGrade
Between GradeMappings.LowGrade
And GradeMappings.HighGrade;

That's "air SQL", but something along those lines ought to work. Note
that you won't be able to display a query with that SQL in design
view -- you have to work in SQL view.

What's supposed to happen with someone whose mark is 94.3? They're not in
any defined range.

You could make a rule that their mark has to be greater than or equal to
LowGrade and less than HighGrade:

SELECT Grades.<some fields>, GradeMappings.GradeLetter
FROM Grades
LEFT JOIN GradeMappings
ON Grades.NumberGrade >= GradeMappings.LowGrade
AND Grades.NumberGrade < GradeMappings.HighGrade

and change your ranges so that they overlap:

95, 100, "A*"
85, 95, "A"
75, 85, "B"
...
0, 29, "N/A"

but then you're going to have a problem with that keener who gets 100%. That
can be solved, though, by setting the maximum range to 101 (or higher if you
give bonus marks....)

 

[Dirk Goldgar]

Dear All,

I have a Query in which you can find a columns with numbers, from 30
to 100, I want these numbers to be converted in to letters (A, B, C,
D, E, F) according to this,

100 : 95 => A*
94 : 85 ==> A
84 : 75 ==> B
74 : 65 ==> C
64 : 55 ==> D
54 : 50 ==> E
49 : 30 ==> F
0 ==> N/A

The letters should appear on a report.

I tried to use (http://www.mvps.org/access/modules/mdl0001.htm) but
it was a little bit difficult to know all the combination of numbers
especially that I might have some fraction 84.3 so maybe someone can
help me with it.

Thanks
Alex

Suppose you have a table named GradeMappings like with fields like
these:

GradeMappings
-----------------
LowGrade (Number/Integer)
HighGrade (Number/Integer)
GradeLetter (Text)

The table would then have records like these:

95, 100, "A*"
85, 94, "A"
...
0, 29, "N/A"

I'm not sure about that last entry, because you don't say what should
happen if -- or if it's possible that -- the number grade is in the
range 1-29.

You could use a DLookup expression in a text box:

=DLookup("GradeLetter", "GradeMappings",
[NumberGrade] & " Between LowGrade And HighGrade")

Or you could build the lookup into your report's query with an
imprecise join:

SELECT Grades.<some fields>, GradeMappings.GradeLetter
FROM Grades
LEFT JOIN GradeMappings
ON Grades.NumberGrade
Between GradeMappings.LowGrade
And GradeMappings.HighGrade;

That's "air SQL", but something along those lines ought to work.
Note that you won't be able to display a query with that SQL in
design
view -- you have to work in SQL view.

What's supposed to happen with someone whose mark is 94.3? They're
not in any defined range.

You could make a rule that their mark has to be greater than or equal
to LowGrade and less than HighGrade:

SELECT Grades.<some fields>, GradeMappings.GradeLetter
FROM Grades
LEFT JOIN GradeMappings
ON Grades.NumberGrade >= GradeMappings.LowGrade
AND Grades.NumberGrade < GradeMappings.HighGrade

and change your ranges so that they overlap:

95, 100, "A*"
85, 95, "A"
75, 85, "B"
...
0, 29, "N/A"

but then you're going to have a problem with that keener who gets
100%. That can be solved, though, by setting the maximum range to 101
(or higher if you give bonus marks....)

I was assuming an integer NumberGrade. Generally, I prefer to do this
sort of thing with only one end of the range in the table, not both
bottom and top, and using more complicated SQL to find the appropriate
record. That method avoids the sort of problem you mentioned, and
eliminates any possibility of overlapping ranges, but it's more
complicated to implement.

 

[Douglas J. Steele]

I was assuming an integer NumberGrade. Generally, I prefer to do this
sort of thing with only one end of the range in the table, not both
bottom and top, and using more complicated SQL to find the appropriate
record. That method avoids the sort of problem you mentioned, and
eliminates any possibility of overlapping ranges, but it's more
complicated to implement.

Tell me about it! (It was my December, 2004 column for Smart Access.
Unfortunately, I can't publish them to my web site until 6 months after
publication)

 

[Dirk Goldgar]

Tell me about it! (It was my December, 2004 column for Smart Access.
Unfortunately, I can't publish them to my web site until 6 months
after publication)

Now see, if I subscribed to Smart Access I wouldn't have to figure these
things out for myself!