Query nine fields and look up

[McFestoe]

I have a db that has nine fields that i want to query it, which if one of these contains a value i need it to be dispaly.

So if field 1 contains something then display it else check feild 2 and so on to field 9.

This needs to be displayed on a report as PanelType = one of the nine fields which ever one holds a value.

 

[Golom]

Your table sounds terribly un-normalized but anyway

Select IIF(NOT IsNull(f1), f1,
       IIF(NOT IsNull(f2), f2,
       IIF(NOT IsNull(f3), f3,
       IIF(NOT IsNull(f4), f4,
       ...
       IIF(NOT IsNull(f9), f9, "No Type Found"))))))))) As [Panel Type]

From myTable

This will display only the first non-NULL field. If others are not null they won't be displayed.

 

[dhookom]

I agree with Golom regarding the un-normalized structure. Another method would be to create a union query that can be used as a subreport.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]