Multiple nested IIf statement (1 Viewer)

[eugzl]

Hi All.
I created query where Location field should be display value based on some conditions. For that I specified for that field nested IIF statement:

Location: IIf([qLocations].[Wing] > " ", [qLocations].[Facility] & " " & [qLocations].[Building] & " " & [qLocations].[Wing] & [qLocations].[Floor] & "-" & [qLocations].[Room], IIf([qLocations].[Wing] = " ", [qLocations].[Facility] & [qLocations].[Building] & [qLocations].[Floor] & "-" & [qLocations].[Room], ""))

As a result I would like to get:
1. [qLocations].[Wing] > " " return value is correct
2. [qLocations].[Wing] = " " I would like to get concatenation of [qLocations].[Facility] & [qLocations].[Building] & [qLocations].[Floor] & "-" & [qLocations].[Room] value but actually result is empty
3. If all concatenated values is null I'm expecting to get empty result.
How to modify the code to get values based on those conditions?
Thanks

 

[theDBguy]

Is this related to your other thread?

How eliminate unwanted character from the field in continuous form

Hi All. The Record Source of continuous form is query where the Location field has concatenation like: Location: IIf([qLocations].[Facility]="ABC",[qLocations].[Facility] & " " & [qLocations].[Building] & " " & [qLocations].[Wing] & [qLocations].[Floor] & "-" &...

www.access-programmers.co.uk

 

[eugzl]

Hi theDBguy. Thanks for reply.
It just similar. In this post I would like to solve the problem inside in the query. Because I found information that Access has nested IIF statement. And in the another post I did know about nested IIF and asked help to solve the problem in VBA code. But if it possible to specify some condition for one field inside in the query for me it is better.
Thanks

 

[theDBguy]

I imagine the answer to this problem is the same as the answer to that other thread. Have you tried it?

 

[eugzl]

I saw the previous answer it suggest me how to convert query code to VBA. And in the previous post code check only two conditions True and False. Here I'm asking how to solve the problem if field has three conditions. And for each condition should be different return value.
Thanks

 

[eugzl]

Now that code version returns correct value for first and second condition:

Location: Switch([qLocations].[Wing] is not null, [qLocations].[Facility] & " " & [qLocations].[Building] & " " & [qLocations].[Wing] & [qLocations].[Floor] & "-" & [qLocations].[Room], [qLocations].[Wing] is null, [qLocations].[Facility] & " " & [qLocations].[Building] & [qLocations].[Floor] & "-" & [qLocations].[Room], [qLocations].[Wing] is null and [qLocations].[Facility] is null, "")

But return value of the third condition still wrong. Need help.
Thanks

 

[eugzl]

The problem is solved. Thanks

 

[theDBguy]

The problem is solved. Thanks

Glad to hear you got it sorted out. I had the feeling the same answer will apply to both questions. Good luck with your project.