Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Complex If Statement

Posted on 2011-10-13
7
Medium Priority
?
217 Views
Last Modified: 2012-05-12
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.

0
Comment
Question by:jtr209
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 15

Expert Comment

by:Eyal
ID: 36965052
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)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36965062
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)

 
0
 

Author Comment

by:jtr209
ID: 36965262
Eyal: does the statement you wrote leave out 4?

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:jtr209
ID: 36965304
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.

0
 
LVL 61

Expert Comment

by:mbizup
ID: 36965335
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

0
 
LVL 15

Accepted Solution

by:
Eyal earned 2000 total points
ID: 36965337
sorry I misunderstood the question
here is it fixed

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,IIf([TBL-SAP Import Working_4].Person_Name Is Null,[TBL-SAP Import Working_5].Person_Name,[TBL-SAP Import Working_4].Person_Name),[TBL-SAP Import Working_3].Person_Name),[TBL-SAP Import Working_2].Person_Name),[TBL-SAP Import Working_1].Person_Name)
0
 
LVL 31

Expert Comment

by:hnasr
ID: 36966676
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...
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question