asked on

Complex If Statement

I currently have the following If stament in a query in an Access database.

Expr1: IIf([TBL-SAP Import Working_1].Person_Name Is Null,[TBL-SAP Import Working_2].Person_Name,[TBL-SAP Import Working_1].Person_Name)

This statement works - however I would like to expand it further.
Continuing the same pattern if [TBL-SAP Import Working_2]. Person_Name is null - then return the value from [TBL-SAP Import Working_3]. Person_Name
Continuing this pattern through if 4 is null return the value for 5.

I am just not sure of the correct syntax to accompish this.
Please provide me with an iff statement which accomplishes this.

Thank you.

Eyal

IIf([TBL-SAP Import Working_1].Person_Name Is Null,IIf([TBL-SAP Import Working_2].Person_Name Is Null,IIf([TBL-SAP Import Working_3].Person_Name Is Null,5,[TBL-SAP Import Working_3].Person_Name),[TBL-SAP Import Working_2].Person_Name),[TBL-SAP Import Working_1].Person_Name)

mbizup

I'd use VBA for clarity:

Function GetWorking(a,b,c,d,e) as Variant
       if "" & a <> "" then
             GetWorking = a
       elseif "" & b <> "" 
             GetWorking = b
       elseif "" & c <> "" 
             GetWorking = c
       elseif "" & d <> "" 
             GetWorking = d
       else
              GetWorking = e
       end if
end function

Open in new window

Save it in a public module that does not have the same name as the function.

Then call it from your query as follows:

Expr1: GetWorking([TBL-SAP Import Working_1].Person_Name, [TBL-SAP Import Working_2].Person_Name,[TBL-SAP Import Working_3].Person_Name,[TBL-SAP Import Working_4].Person_Name,[TBL-SAP Import Working_5].Person_Name)

jtr209

ASKER

Eyal: does the statement you wrote leave out 4?

jtr209

ASKER

mbizup: I am not really familiar with using modules and when I pasted in the VBa and ran the query I got a syntax error. Im not sure what I did wrong.

mbizup

Sorry - I forgot to include the "Thens"

This should compile for you:

Function GetWorking(a, b, c, d, e) As Variant
       If "" & a <> "" Then
             GetWorking = a
       ElseIf "" & b <> "" Then
             GetWorking = b
       ElseIf "" & c <> "" Then
             GetWorking = c
       ElseIf "" & d <> "" Then
             GetWorking = d
       Else
              GetWorking = e
       End If
End Function

Open in new window

ASKER CERTIFIED SOLUTION

Eyal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hamed Nasr

Syntax:
IIF(true1, v1, IIF(true2, v2, IIF(true3, v3, IIF(true4, v4, v5 ))))

replace
true1 by: Nz([TBL-SAP Import Working_1].Person_Name, "true1")<>"true1"
v1 by: [TBL-SAP Import Working_1].Person_Name

follow same idea to replace others...