I need to use a 4 level else if statement
(if..then..else if .. else if.. etc.)
I do not know the rihgt format for typing this in sql view of my union query. Any suggestions.

Thanks

Please, let s see you present SQL statment and explain better what you want to achieve with your if s.
It s likely that you ll have to use VBA to achieve your goal.

Alex

Here's part of my query. Checking for nulls in field2, then field one, else field1 and field2. I actually need to check for nulls in fields2 thru field4, and be able to combine up to these four fields for my result.

UNION ALL SELECT [table1].[ID], 8 as sort,
"field1:" As Label, IIf(IsNull([table]![field2]),
[table]![field1],
[table]![field1] & " " & "&" & " " &[table]![field2])
AS [value]
FROM [table] WHERE ((([table].[field1]) Is Not Null))

Thanks

You cannot do it like that. As it is often the case when you want to design a complex query, you will have to break the process further down into more elementary steps. You need to build intermediary queries so that the last one will display in one field either [table]![field1] or [table]![field1] & " " & "&" & " " &[table]![field2].

For example:
-Querym will select [table]![field1] from [table] where [table]![field2] is null into a field named Tempo
-Queryo will select [table]![field1] & " " & "&" &" " &[table]![field2] where [table]![field2] and [table]![field1] are not null, into a field also name Tempo.
-queryp will make the union of both...


If you explain the strucure of table1, table, and what you try to achieve, we could try to go through it together.

Alex


[This message has been edited by Alexandre (edited 09-24-2001).]