Change Category Code

[valgore]

I made a report that has category codes shown. they are like AU or PO or CO. what i want to do is say

If category code = AU, the the field should change to Auto. or if PO then Postage

Is there a way to do this for a report?
i tried it, but it said that i couldn't alter that field
Im using MS Access 2003

 

[sxschech]

In the textbox of the report

=iif([category code]="AU","Auto",
 iif([category code]="PO","Postage",
 iif([category code]="CO","Co")))

 

[dhookom]

You should have a category table that contains each unique category code with the appropriate description. Then add the category table to your report's record source so you can display the description rather than the code.

Duane
Hook'D on Access
MS Access MVP

 

[valgore]

ok i tried what you said sxschech, but it said the expression was too complicated .

dhookom, ill have to talk to my superior about that. i do have a table currently, but its set up with only one field. and the field looks like Auto-AU or Meals-ME. they wanted code to take that field and make it AU or ME, but they want to be able to see Auto-AU during the time of selection. so i cant really change it.


Valgore

 

[dhookom]

Am I correct that users are entering values like AU or ME into a field and you don't have a table with AU, ME, etc stored as the primary key to the table. But, you do have a table with a single field and values like "Auto-Au" and "Meals-ME"?

I think this is not a good structure. You can create a query that can serve as the table like:

SELECT DISTINCT Left([only one field],Instr([only one field],"-")-1) As Title, Mid([only one field],Instr([only one field],"-")+1) As Code
FROM [table currently]

You can then add this query to your report's record source so you can display the Title rather than the code.

Duane
Hook'D on Access
MS Access MVP

 

[valgore]

its a drop down list with the code. i will try that and get back to you.

Valgore

 

[dhookom]

Do you have a combo box with a type of Value List rather than Table/Query? If so, I would change it to use a table/query of the unique codes and titles.

Duane
Hook'D on Access
MS Access MVP