IIF Statement in Query

[Juliet]

Hi

Have four columns in a query. Columns are text. Want to do an IF statement - would usually do a IF(ISTEXT)) in excel. So in my query I have started with the following:

NewStyle: IIf([GroupA - Quantity]![Inventory.Style]="",[GroupB - Quantity]![Inventory.Style],[GroupA - Quantity]![Inventory.Style])
Based on and IF(ISTEXT) statement - if GroupA is empty then enter GroupB.
The result is only GroupA is entered - blank for GroupB - so obviously won't work when I extend it out to GroupC or GroupD.
Any ideas?
Thanks, Juliet

 

[Atomic Shrimp]

You can nest IIf statements to make them handle more than two possible outcomes, but it can start to get quite messy, quite quickly.

Am I right in thinking you have multiple tables for different groups of records that are the same kind of data? If so, that's not a good idea and fixing it would probably solve your problems now and in the future, so instead of having similarly structured tables Group A, Group B, etc. you'd have one big table, with an additional column named 'GroupID', and put A, B, C etc. values in that column to denote which group each record belongs to.

You can always get that single big table to behave like separate ones later by running a query selecting just one group

 

[Juliet]

Hi

The 4 group tables have to be separate as they each have a multiple toggle list in them.

So I have three IIf statements in my query between group a and b then between group c and d. The third IIf statement is between IIf One and Two. This seems to have worked.

The IIF statement gives me the data from the sub list in the multiple column.

Thanks for your help.

Juliet

 

[Atomic Shrimp]

Hi

The 4 group tables have to be separate as they each have a multiple toggle list in them.

So I have three IIf statements in my query between group a and b then between group c and d. The third IIf statement is between IIf One and Two. This seems to have worked.

The IIF statement gives me the data from the sub list in the multiple column.

Thanks for your help.

Juliet

No problem - I'm glad it seems to be working now. However, I would be negligent if I didn't strongly advise you to consider where your table design choices might take you in the future...

If you have four tables containing the same kind of items, that's always going to make things awkward - as it has here, a bit - and worse in the future.

I'm not sure what you mean by toggle groups for each of the four tables, but if it's something like a different assortment of attributes for each of the four different groups of items, then again, creating them as individual yes/no fields (if that's what you've done) may not be the best solution.

I don't mean to preach, and I appreciate this kind of advice can appear unnecessarily complex, but I have learned this: not only does normalized database design become more essential as an application matures, but it also becomes more difficult to implement as the application matures - because more an more of it is dependent on the original structures.

I'll say no more, for I expect I have already been far too pushy, but if you would like some sound advice on db design, I'm sure the folks here will be eager to help - just describe what you're trying to achieve.