Is it possible for an append query to also do a find/replace text at the same time?

I have an append query that saves to a new table. The one column is a colour and is abbreviated. The problem is the client wishes the acronyms in this field to be filled out in full when it appends. Is this possible or do they have to do it manually each time?

e.g. The office enters CMYK as the colour but the end customer wants the printout to show Cyan Magenta Yellow Black since they don't understand the abbreviations.

Thanks

Hi VikingWheel (what is your name?)

yes

is CMYK the only abbreviation that you want to expand?

If it is, you can do something like this:

field: Color_: IIF (nz([color_fieldname],"")="CMYK","Cyan Magenta Yellow Black", [color_fieldname])

If not, make a table for the abbreviations. Link to the table on the abbreviated field and choose the expanded version for the query.

If you are wanting to expand each term and making a table isn't practical, it would be best to send the color code to a function, have the function expand it and send the expanded value back

Hi

Well, CMYK is the only abbreviation but on reviewing all of the data it will have many variations like "c,m" " c,y,k," etc. AND it is in a group of words in the field (e.g. Sub Grade 4323, 533 c,m,y,k, 235, 344, 544, 432.

What I would love is to be able to do a generic replace stating if "C," or "c," or "C", or "c" change to Cyan etc.etc.

Would the above IIF statement still work and if so where does that go?

Jody

Hi Jody,

are colors always separated with comma?

"AND it is in a group of words in the field "

do the abbreviations always follow numbers? there has to be something to look for so the right substitutions are made. What are more examples of your data?