IIf Statement in query

[eacollie]

I have four fields in my table: [Tables] (integer), [Chairs] (integer], [TheatreStyle] (yes/no), [ClassroomStyle] (yes/no) and some may be null.

Is it possible to nest these together using IIf in my query to display as follows: [Tables] Tables, [Chairs] Chairs, ([TheatreStyle] or [ClassroomStyle]. I know I have to nest the IIf statement but can't figure out how.

Thanks much!

 

[plog]

Nesting means putting one function inside another. You do not need to do that with what you want to accomplish. What you want to do is concatenate. You do that with the ampersand (&):

YourDataFieldName: [Tables] & " Tables, " & [Chairs] & " Chairs, " & Iif(TheatreStyle, "Theatre Style") & " " & Iif(ClassroomStyle, "ClassRoom Style")

You said you want [TheatreStyle] or [ClassroomStyle], however that's not how you have set up your data. With the way you set up your data those are not related at all. A record could be both classroom style and theater style, or neither classroom style nor theater style. If that's not the case and they are mutually exclusive, then you only need one field to captrue that data, not one field for each style.

 

[Minty]

What is your desired result ? Can you post up what your expecting as the output?

 

[eacollie]

I was hoping to get the following (as examples):
1. If there are tables to be set up - 5 tables
2. If there are chairs to be set up - 50 chairs, theater style or 50 chairs, classroom style
3. If there are tables AND chairs - 5 tables, 50 chairs, theater style
4. If no tables or chairs - blank

Thank you

 

[arnelgp]

SELECT (iif(nz([tables],0)<>0,([tables] * 5) & " tables, ", null) & iif(nz([chairs],0)<>0,([chairs] * 50) & " chairs, ", null) & iif([theatreStyle]," theater style", null) & iif([classroomstyle], "classroom style", null) ) As Style From MyTable;

 

[eacollie]

SELECT (iif(nz([tables],0)<>0,([tables] * 5) & " tables, ", null) & iif(nz([chairs],0)<>0,([chairs] * 50) & " chairs, ", null) & iif([theatreStyle]," theater style", null) & iif([classroomstyle], "classroom style", null) ) As Style From MyTable;

Thank you so much arnelgp!

 

[arnelgp]

You are welcome