Complicated IIf statement

[hlock]

Using Access 2007 on XP - I need to label the type of product for each record
based on certain criteria. There are about 200,000 records right now.
Depending on variations of Division, Product, Program, Aslob, Subline and
Type of Loss, each record will get a new label. The only problem is that for
some products, I look at combinations of Division, Product and Program. For
other products, I look at combinations of Aslob, subline and type of loss.
My original plan was to use IIf statements like I do in AS400, but it became
very complicated AND Access cut off my statements halfway through (I guess I
had too much). Then, I started to create a table for lookup, but I don't use
all of the criteria for each record so a lot of fields are blank. Does
anyone have any suggestions? Thanks in advance.

 

[NTC]

consider that you can segment that IIF into separate fields. (probably)

it uses up fields but can sometimes make a multi level nested IIF statement
easier to troubleshoot...

since you mention sometimes you use "these" 3 fields and other times "those"
3 fields...you maybe able to IIF them in separate columns and then create a
third column that selects between those two...

 

[John W. Vinson]

Using Access 2007 on XP - I need to label the type of product for each record
based on certain criteria. There are about 200,000 records right now.
Depending on variations of Division, Product, Program, Aslob, Subline and
Type of Loss, each record will get a new label. The only problem is that for
some products, I look at combinations of Division, Product and Program. For
other products, I look at combinations of Aslob, subline and type of loss.
My original plan was to use IIf statements like I do in AS400, but it became
very complicated AND Access cut off my statements halfway through (I guess I
had too much). Then, I started to create a table for lookup, but I don't use
all of the criteria for each record so a lot of fields are blank. Does
anyone have any suggestions? Thanks in advance.

I'd try to use the lookup table (NULL fields don't take up any room and have
only minor effects on performance) if possible, and a custom VBA function if
not. IIF() statements are inefficient and hard to write (and debug!) when they
get complicated. If your label just depends on six inputs, it should not be
hard to do it either way.