RE: Converting text to a number in query

Oohh, this is very close. It's replacing numbers for text for just G, Y, and
R, but not the text with the various ++'s and --'s. Any ideas?

Also, you asked if I wanted this to store the info back into my table. I
don't think so as long as I can generate my report with the letters, but
using the numbers to rank them. (I did this last year by exporting/importing
this info into Excel and then back again. I'm hoping I don't have to keep
doing that.)

Thanks for your help, if this works, my life just got easier :~)
--
SS


"KARL DEWEY" wrote:

I have three separate columns that will be populated
First your table structure is wrong as you are using a spread*** format.
Instead of three fields for grading use one to identify who did it and a
second for grade.

As I said you have too many to use nested IIFs.
A translation table will work for you. The translation table would look
like this --
Data Value
G++ 1
G+ 2
G 3
G- 4
G-- 5


Then to use it in design view of your query add the translation table --
Fields: Field1 Field2 Data Value
Table: Main Main Trans Trans
Criteria: Main.Field3

Main.Field3 is the field of your table that comtains the Gxx information.
The Value field will output the numerical of the G++.

I seem to get the impression you want to store this translation back into
your table. Is that correct?

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

I didn't understand what you were asking me to do, so I thought I'd clarify a
little and explain the purpose of these fields.

I work with documents that are rated on the following scale: G++, G+, G, G-,
G--, Y++, Y+, Y, Y-, Y--, R (G++ is the best, R is the worst). Three
different people will rate the same document so I have three separate columns
that will be populated with any of the above.

I want to convert the letters to: 1, 2, 3, 4, 5 ,6, 7, 8 ,9, 10, 11 (1 is
the best, 11 is the worst). After which I will average the 3 number values
to rank the documents best to worst. (I have that formula already.)

I managed to get the following formula to almost work:
Field1: IIf([Document 1]="G++",1)
except it won't populate any numbers, everything is blank, even if I change
the number value to 20, it won't populate.

I appreciate any additional help with this. Thanks.
--
SS


"KARL DEWEY" wrote:

You can use nested IIFs but if you list is very long then a translation table
would work better.
Data Value
G++ 1
G+ 2
G 3
G- 4
G-- 5

Then to use it in design view --
Fields: Field1 Field2 Data Value
Criteria: Field3

Field3 is the field of your table that comtains the Gxx information.

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

I'm having trouble with a conversion in order to get a formula to work. I
have text that = numbers. Example: G++=1, G+=2, G=3,G-=4,G--=5, etc. I
need a formula to convert G++ to 1 so that it can be totalled. Thanks for
any help.
--
SS
.