text data to numeric data

[otrivine]

hello there

i have one table has a column called evaluation ( text data type ), and i want to take the data and convert it into a number data type in another table or a query , something like the following
excellent=90 to 100
good = 80 to 89
accepted = 60 to 79
under 60 is failed

please help , thanks in advanced

 

[Alansidman]

What are the values in the current table? Are they the numeric values but in text format? or are they the Excellent, Good, etc.

 

[otrivine]

thanks for the quick response
they are just like this

excellent
good
accepted
weak
(the user chooses one of the four "comboBox"

and i want the other table or query in number 0 to 100

the purpose is to make a simple bar graph, check the attach

 

[vbaInet]

You still haven't made yourself clear. Do you want, for example, Any number between the range 90 and 100 to display Excellent? Or Do you want Excellent to display the string "90 to 100"?

 

[Taruz]

Hi..

This function allows you want..:

Public Function trz(scr) As String

Select Case scr
    Case 90 To 100
    trz = "Excellent"
    Case 80 To 89
    trz = "Good"
    Case 60 To 79
    trz = "Accepted"
    Case Is < 60
    trz = "Failed"
End Select

End Function

I used this function in a query report source..:

select trz([Score]) as scr, * 
from tblScores 
where trz([Score])=
           iif([Forms]![Form1]![cmbtt] ="All",trz([Score]),[Forms]![Form1]![cmbtt])

After selecting the value from the form, report opens..

 

[vbaInet]

It's the other way round Taruz. The source is the text strings "Excellent, Good, Accepted and Failed".

 

[otrivine]

well, thank you both and let me put it like this
the table is The source just like vbaInet said, and i want the query:

table query(or another table)

Excellent = 100
good = 75
accepted = 50
weak < 50 ( any number under 50 )
-

 

[vbaInet]

You are doing things the odd way round.

Anyway, here's what you can do:

Create a table with two fields, Score (Number - PK) and Evaluation (String). The table will look like this:

Score | Evaluation
1       Failed
.
.
.
59      Failed
60      Accepted
.
.
.
79      Accepted
80      Good
.
.
.
89      Good
90      Excellent
.
.
.
100     Excellent

Fill in the rest and link that to the Evaluation Column. You can even further normalize this.

 

[otrivine]

thank you so much vbaInet
i appreciate it