Analysing a record in VB

[rafi2]

Can anyone provide me some tips as to how to pass a record to VB in order to analyse it? What I'm trying to do is show a specific field from a record, based on a user's input. I have successfully used the Expression Builder with 14 nested iif statements. When I try to add a 15th iif statement I get an error message saying "The expression you entered is too complex". I know I can create a custom function using the Select Case function to land on the field I want to report, however, I am not familiar with how to pass the record to VB to look at it.

My current expression looks like this:

([Forms]![Search CAS]![#] is a user-entered parameter)

=IIf([Forms]![Search CAS]![#]=[1CAS Number],[1ING],IIf([Forms]![Search CAS]![#]=[2CAS Number],[2ING],IIf([Forms]![Search CAS]![#]=[3CAS Number],[3ING],IIf([Forms]![Search CAS]![#]=[4CAS Number],[4ING],IIf([Forms]![Search CAS]![#]=[5CAS Number],[5ING],IIf([Forms]![Search CAS]![#]=[6CAS Number],[6ING],IIf([Forms]![Search CAS]![#]=[7CAS Number],[7ING],IIf([Forms]![Search CAS]![#]=[8CAS Number],[8ING],IIf([Forms]![Search CAS]![#]=[9CAS Number],[9ING],IIf([Forms]![Search CAS]![#]=[10CAS Number],[10ING],IIf([Forms]![Search CAS]![#]=[11CAS Number],[11ING],IIf([Forms]![Search CAS]![#]=[12CAS Number],[12ING],IIf([Forms]![Search CAS]![#]=[13CAS Number],[13ING],IIf([Forms]![Search CAS]![#]=[14CAS Number],[14ING],""))))))))))))))

Any help is greatly appreciated.

Thanks!

 

[RV]

Are 1CAS Number, 2CAS Number, et cetera, by any chance table column names?

RV

 

[rafi2]

Yes, they are indeed table column names. As are 1ING, 2ING, 3ING, etc...

 

[RV]

Smells like you've got a flat table...
You should create a new table and store your data as rows.
You then can refer to a specific value rather then skiiping through your columns which is rather inconvenient as a result of incorrect datastructure design.

RV

 

[rafi2]

That's exactly what I have...a flat table. I do realize this is a poor design, but is there a way I can do what I had originally thought to do (ie, writing a custom function to do the search)?

If I were to re-do the structure of this, can anyone suggest how I should lay out my tables? Currently, each record essentially consists of this, all stored in a single table:

Item Name
1Ingredient, 1Number, 1%
2Ingredient, 2Number, 2%
...
15Ingredient, 15Number, 15%

I'm a bit new to this stuff, so please bear with me.

Thanks!