Convert Number to Text!

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

You could create a table with the values 30 to 1000 and a
second column with the A B C D E F. Then reference that
table when necessary

Eric

-----Original Message-----
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


.

Thanks, I know this, but I dont is the best thing to do, I think it is
better to make a module and apply it to the report.
If no one have an aswer I'll use your idea.
Thanks alot,

"Eric Blitzer" wrote in message
...
You could create a table with the values 30 to 1000 and a
second column with the A B C D E F. Then reference that
table when necessary

Eric

-----Original Message-----
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


.

Alex, substitute your fieldname for the [Field1] I used.
IIf([Field1]30,"N/A",IIf([Field1]50,"F",IIf([Field1]55,"E",IIf([Field1]65,"D",IIf([Field1]75,"C",IIf([Field1]85,"B",IIf([Field1]95,"A","A*")))))))
This should do it.
Hope this helps.
Fons
"Alex" wrote:

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

Alex wrote:
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.


I agree with Ken that a table is a good idea, but I would
use three fields:
Low High Grade
0 29 N/A
30 49 F
50 54 E
55 64 D
65 74 C
75 84 B
85 94 A
95 100 A+

then join that table to your existing table using a non-equi
Join

SELECT table.number, grades.Grade
FROM table INNER JOIN Grades
ON table.number = Grades.Low
AND table.number = Grades.High

OTOH, for a quick and dirty solution you could use the
Switch function directly in a query.

SELECT number, Switch(number30,"N/A", number50,"F",
number55,"E", number64,"F", number74,"D", number84,"C",
number94,"A", number100,"A+"
FROM table

--
Marsh
MVP [MS Access]

I am assuming the tables that contains these scores is called [GRADE]
In your report, have [GRADE] appear in the report, and do the following:
1. Create an unbound text box
2. Right-Click it and select properties
3. Set the control source with the following line:

=switch([GRADE]94,"A*",[GRADE]84 AND [GRADE]95,"A",[GRADE]74 AND
[GRADE]84,"B",[GRADE]64 AND [GRADE]75,"C",[GRADE]54 AND [GRADE]65,"D",
[GRADE]49 AND [GRADE]55,"E",[GRADE]29 AND [GRADE]50,"F", [GRADE]30,"N/A")


Now you can either keep the [GRADE] on the page, or you can make disappear
by setting the VISABLE option on the properties menu to NO.

For future reference, if you use a number to text conversion that uses a
number range like this one, make sure that the lower value is placed first;
notice how I arranged the lower values in comparison to the upper value.
[GRADE]84 AND [GRADE]95

Think in terms of X
84 X 95
The same logic applies, the lower value appears first.