Conditional Query for selecting different fields for each record

[RKoller]

I have an interesting report query I am working on. Is it possible to have a query that shows different fields for individual records depending on the content of the record? Let me see if I can explain. Let's say that I have a Status field that can be "Complete", "Active", or "Planned". And lets say also in that table I have Field1, Field2, and Field3. For each record, I don't want to include all three fields (Field1, Field2, Field3), but only one of these fields depending on what is in the Status field.

So, it would be something like:

Select Status, (if Status = "Complete", select Field1), (if Status = "Planned", select Field2), (if Status = "Active", select field3) FROM table;

So, the query would result in 2 fields for each record: the status field, and one of the three other fields depending on what is in the status field for that record.

Thank you kindly for your help.

 

[pbaldy]

Look at the IIf function in Help. You'll want to embed one within the false argument of another.

 

[llkhoutx]

Either branch in VBA code on "Status" to execute the appropriate query, or, dynamically build the SQL string for the query.

The first method is more straight forward.

Select case sStatus
Case "ACITVE"
DOCMD.OPENQUERY "QRY FILTER ACTIVE"
CASE "PLANNED"
DOCMD.OPENQUERY "QRY FILTER PLANNED"
CASE "COMPLETED"
DOCMD.OPENQUERY "QRY FILTER COMPLETED"
END SELECT