Long Expression in SetValue Macro

[DMurray3]

Hi all,

On a Form, I am trying to write a macro ta will "calculate" the full product name based on the product's attributes (and abbreviations I have in the corresponding attribute tables.

The formula is:
=IIf([txtFamilyAb]="No Definido";"Producto No Definido";IIf([txtFamilyAb]="PROD";"PR" & " " & IIf([txtSubLine]="No Definido";"";[txtSubLineAb]) & " " & IIf([txtType]="No Definido";"";[txtTypeAb]) & " " & IIf(Len([Brand])<=1 Or [Brand]="NA";"";Left([Brand];15)) & " " & IIf(Len([Model])<=1 Or [Model]="NA";"";Left([Model];10)) & " " & IIf(Len([Dimen])<=1 Or [Dimen]="NA";"";Left([Dimen];15)) & " " & IIf(Len([cboMaterial])<=1 Or [cboMaterial]="NA";"";Left([cboMaterial];15)) & " " & IIf([Warp]="NA";"";IIf([Warp]="Blanco";"U-B";"U-N")) & " " & IIf([Weft]="NA";"";[Weft] & " COL") & " " & IIf([Stiffness]="NA" Or [Stiffness]="Ninguno 0%";"";"APRES") & " " & Switch([Present]="Rollo";"ROLLOS";[Present]="CorteRecto";"C-RECT";[Present]="DobladoExtremos";"CD-EXTR";[Present]="DobladoMedio";"CD-MED";[Present]="Trapecio";"CD-TRAP";[Present]="NA";"");"SRV" & " " & IIf([txtSubLine]="No Definido";"";[txtSubLineAb])))

Unfortunately, the Expression builder does not allow the length of the formula.

Can any one provide guidance and a sample of breaking up this long formula and how to set it in the Macro ? Although I prefer to do this with a macro tied to a button on the form, Ias as a second alternative, could you provide a sample VBA to assign to a "*******" event.

Many thanks for your help.

Kind regards,
dmurray3

 

[Joe4]

With anything that big and long, I would recommend creating your own function in Access.
See: Creating and Using User Defined Functions in Access – Bacon Bits