Calculated Field - Removing "Data"

[huntersoasis]

First let me describe the issue this database is not "Data normalization" but is required to be this way because of the way it is used in export to a MYSQL site.


so while data normalization would resolve the issue as far as the problem i have it would not work for the other direction.




A field in the database is a calculated field. and reads as this.

IIf([PRODUCT_MENU_ORDER]=9,"Out Of Stock",IIf([CATEGORY_1]="","",[CATEGORY_1]) & IIf([CATEGORY_2]="","","|"+[CATEGORY_2]) & IIf([CATEGORY_3]="","","|"+[CATEGORY_3]) & IIf([CATEGORY_4]="","","|"+[CATEGORY_4]) & IIf([CATEGORY_5]="","","|"+[CATEGORY_5]) & IIf([CATEGORY_6]="","","|"+[CATEGORY_6]) & IIf([CATEGORY_7]="","","|"+[CATEGORY_7]) & IIf([CATEGORY_8]="","","|"+[CATEGORY_8]) & IIf([CATEGORY_9]="","","|"+[CATEGORY_9]) & IIf([CATEGORY_10]="","","|"+[CATEGORY_10]) & IIf([CATEGORY_S_BIKINIHOUR]="","","|"+[CATEGORY_S_BIKINIHOUR]))

The issue that in place is at times there are "Categories" that can not be used while this calculated field takes 10 fields and creates them into one field with a option of another controlling it.


Can anybody think of a way to limit it to where if a "Category" field has the words "Dress" or "Shirt" it removes it while still completing the calcualation? can it be done in query? get me a idea. I did have a calculation that would work but ran into issue of a "Calculated field" can only have a calculation of 2048 char and the calculation that was written was well over 2048.


I will give you a idea of the field now.


CATEGORY_1,CATEGORY_1,CATEGORY_1,CATEGORY_1,CATEGO RY_1,CATEGORY_1,CATEGORY_1,CATEGORY_1,CATEGORY_1,C ATEGORY_1
Dress,Shirt,Pants,Black,Long,Short,Lace,Top,Bottom ,Skirt,


and the Calculated Field would produce from the above if

 [Product_Menu_Order]

was not a 9
CALCATEGORY
Dress|Shirt|Pants|Black|Long|Short|Lace|Top|Bottom |Skirt


and what I want to happen is for somehow to remove in the calulated field "Long" and "Bottom" to make it




Dress|Shirt|Pants|Black|Short|Lace|Top|Skirt




Thanks for Any input.

[ranman256]

1. if you have to use THAT many IIF then you should think about resorting to building a function.

2. Your IIF statement makes no sense,
in IIf([CATEGORY_1]="","",[CATEGORY_1])
if the value CAT1 = "", then it will show "" , then there is no need at all for the IIF.
you will get the same result by just showing: [CAT1]
without the IIF.

[ssanfu]

I have to agree with ranman256, if you can't/won't normalize your database, you will have to use/write a UDF.
IMO, it would be easier to have a normalized structure and arrange the data to be exported to MySQL, than to fight the problems of a non-normalized structure.

Allen Browne has a function to concatenate multiple values at http://www.allenbrowne.com/func-concat.html.
You could/can modify the code to remove "Long" and "Bottom" in the result.