Converting a Code to Text

[rmcafee]

I have a list of 30 codes that I want to covert to text. When the code is assigned to an entry, I would like it to display certain text.

Here is an example of one of them.
The code "01P" would equal the text "Accepts Responsibility".
The code appears in the field labeled Comment1.
New to Access so any help would be appreciated.

[Robeen]

Do you have your Codes and the Descriptions in a Table?

[rmcafee]

Yes, the I have the Codes and Descriptions in a separate table/query.

[Robeen]

Where is this happening? Is it on a Form?

If it is on a Form [Data Entry?], you can provide the User with a Combo Box that has all the Codes [and descriptions if you want] showing and when the User selects a Code - the description can be displayed if that is what you need.

If you post a few more details, I can give you a better answer.

[rmcafee]

I have all my data in a query. Some of the entries in the Comment1 field (column) have these various codes in them. I want the text to display instead, OR create another column that converts the code to the desired text. I've tried creating another column in the query and I've tried using something like Text Comment: IIF([Comment1]= "01P", "AcceptsResponsibility") but there are 30 of these and I can't figure our how to make so many work?

[Robeen]

Ok. I think I understand now.

You want to do all this IN a Query!

Sorry i didn't GET that before!!

I think you want something like this in the new field:

Description: IIf([Code]="01P","Accepts Responsibility",IIf([Code]="02P","Second Description",IIf([Code]="03P","Third Description")))

Each time you add a new IIF, you will have to add another closing parenthesis at the end.

Let me know if this helps.

I actually think you shouldn't be doing this.

It looks like you should have all your Codes and their Descriptions in a separate Table.

Once you have them in the Table, you don't need to write 30 IIF clauses in a query to match your 30 Codes and provide Descriptions. You can just get the descriptions from the Codes/Descriptions Table.

Let me know if you have any questions about this.

[rmcafee]

Yes, when I tried doing all thirty it said it was too complex.
Yes, I have the descriptions listed with the codes in another table/query for reference (they aren't matched with any data) but how do I get them to display the text if I only have the codes in my main data query. So I guess my question is how do I "get the descriptions from the Codes/Descriptions Table" to display from the codes listed in the second table?

Another thing I should've mentioned. There are two Comment columns in the query Comment1 and Comment2, each column has the codes in them.

I think I've gotten it to work just now by joining the properties to two queries that contain the Codes/Comments. I made a second copy of my Codes/Comments, linked one to Comment1 and the other to Comment2, went in to "Join Properties" on the link and got them to display everything.

Looks like it works - Was this what your were thinking I should do instead of the IIF Clauses?

Thanks for your help, you got me thinking of the alternatives I needed.

[TinaCa]

I use this alot.

I want to show the description of something from a code table but not the unique identifier for it. i.e. 1 blue, 2 red, 3 yellow. I have been creating a text box bound to the field that needs the code (like ColorID). Then when I create the combo box I hide column 1 (i.e. column count = 2, column widths 0,1). You know when you create a combo box you can tell it to Store the value in this field just select the field the text box is bound to .