If Statement with multiple condition

[TIO OLAN]

Hi!

I am new to Access formula building and could someone help me to convert this Excel formula (IF statement) to be apply on the Access (design view) query?

Excel formula: IF(AND(A2="",B2=""),C2,IF(A2<>"",A2,IF(A2="",B2,IF(B2="",C2,C2))))

I don't know SQL so it is possible to achieve this in the normal design view using the IIF statement?

Image attached here is the sample data in excel and I want to get the same result when in access database. Thank you very much

 

[GaP42]

Something like:

SELECT zzzjunctionTbl.ClassID, zzzjunctionTbl.TeamID, zzzjunctionTbl.EmployeeID,
IIf((Nz([ClassID],0))=0 And (Nz([TeamID],0))=0,[EmployeeID],
IIf((Nz([ClassID],0))>0,[ClassID],
IIf((Nz([TeamID],0)>0),[TeamID],[EmployeeID]))) AS Result
FROM zzzjunctionTbl;

The function NZ is used to avoid comparison to null as I suspect the table uses foreign keys to other tables and so likely contains nulls rather than 0.
If the item is null then it returns a 0, in case you do have 0 in the table for an ID
The last if in your excel function is redundant - it returns employeeID despite any difference in the criteria

 

[cheekybuddha]

You should be able to use:

SELECT
  A,
  B,
  C,
  IIf(Nz(A, '') = '' AND Nz(B, '') = '', C, IIf(Nz(A, '') <> '', A, B)) AS ABC
FROM YourTable;

 

[arnelgp]

SWITCH(IsNull([Class ID]) = False, [Class ID], IsNull([Team ID]) = False, [Team ID], True, [Employee ID]) As [Valid ID])

 

[GaP42]

Correction?

SELECT
  A,
  B,
  C,
  IIf(Nz(A, '') = '' AND Nz(B, '') = '', C, IIf(Nz(A, '') <> '', A, B)) AS ABC
FROM YourTable;

to:

SELECT A,B,C,
        IIf(Nz(A, '') = '' AND Nz(B, ''), C,
        IIf(Nz(A, '') <> '', A,
        IIF(Nz(B, '') <> '', B, C))) AS ABC
FROM YourTable;

 

[DHookom]

There is sufficient complexity that I would create a small user defined function that has the three fields as arguments and returns a string. You can add comments and use the function anywhere that you would have used the complex expression.

 

[cheekybuddha]

Correction?

The last IIf() is redundant (*I think!*) - it is effectively the same as the first test (A = '' AND B = '')

 

[cheekybuddha]

I didn't look at the attached image before ( )

Arnel has a good suggestion.

Using IIf(), it could be more simply written:

SELECT
  [Class ID],
  [Team ID],
  [Employee ID],
  IIf(
    Nz([Class ID], '') <> '',
    [Class ID],
    IIf(
      Nz([Team ID], '') <> '',
      [Team ID],
      [Employee ID]
    )
  ) AS Result
FROM YourTable;

 

[cheekybuddha]

And most simply, if each field can only either have a value or be NULL, then you can have:

SELECT
  [Class ID],
  [Team ID],
  [Employee ID],
  Nz([Class ID], Nz([Team ID], [Employee ID])) AS Result
FROM YourTable;