Assign field text value a numeric value in a query expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a query expression for an existing text field and I am
having problems doing so.

Example:
The existing text field is called Wound Class. I have pulled this field
into a query from a table. This text field has four values: A. CLEAN B.
CLEANCONT C. CONTAMINATED D. DIRTY
I am trying to create an expression to create a numeric values to be assigne
to each text value.
CLEAN = 1, CLEANCONT = 2, CONTAMINATED = 3, DIRTY = 4

Any suggestions?
 
I would suggest creating a Table with two field (Id , Description) that will
contain the values that you specified.
Store in the MainTable the Id and not the description, and when you want to
display te description create a query that link this two tables.

If you don't like this approach you can create a query and use the Switch
function

SELECT FieldName,
Switch([FieldName]="CLEAN",1,[FieldName]="CLEANCONT",2,[FieldName]="CONTAMINATED",3,[FieldName]="DIRTY",4) AS FieldId
FROM MyTableName

But any spelling mistake will return NULL.
 
Back
Top