How to change the data in a column to something else? (1 Viewer)

[Espinoza84]

Hello all,
I have a table with a column called "DIR FILE".
Now in this column the values are " RX RX", "RWXD", and "all all". anyways what RX RX means is "read-only', RWXD means "read-write-excute-delete, and "All all" means all.

I want to run a query, and select this field, but I do not want it to display RX RX, RWXD, or all all, instead I want for the query to display read-only, read-write-execute-delete and all,
anybody know the code i would have to put in the "Field:" section in the Query Design view,
thanks.

 

[KenHigg]

MyNewField: iif("RX RX","read only",iif("RWXD","read-write-execute-delete","all"))

???

 

[georgedwilkinson]

Why not create a lookup table with the smart codes and the english translation? You could then join on that table in your query.

 

[KenHigg]

If there is ever the chance that you'll need this conversion done again in another report or form or if any of the values will change then I'd do what George suggests as well -

 

[Espinoza84]

the code is not really working for me?

I am using this code(yes the spaces are part of the column name/data):

Type of access: IIf("R X R X ","read only",IIf("RWXD RWXD ","read-write-execute-delete",IIf("all all ","all")))


but the results is "read only" for everything. I fail to see how it knows which field to pull the data from as Im not specifying it.

 

[KenHigg]

Duh, my bad:

MyNewField: iif([DIR FILE]="RX RX","read only",iif([DIR FILE]="RWXD","read-write-execute-delete","all"))

 

[Espinoza84]

attaching a screenshot

 

[Espinoza84]

ok thanks a lot its working now!

"Why not create a lookup table with the smart codes and the english translation? You could then join on that table in your query."

- I am not sure how to accomplish, I've never heard of a "lookup table"?