Assign field text value a numeric value in a query expression

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian - 23 Apr 2006 19:43 GMT

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?

Reply to this Message

Ofer Cohen - 23 Apr 2006 20:36 GMT

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.

Signature

HTH, Good Luck
BS"D

> I am trying to create a query expression for an existing text field and I am
> having problems doing so.
[quoted text clipped - 8 lines]
>
> Any suggestions?

Reply to this Message