Nested IIF's (1 Viewer)

[atrium]

I'm trying to create a field in my Book Mark source file to go into a merge with a set of precedent documents

If the matter is in the Magistrates court i.e. len([CourtRefNo]) is > 0 I want to use the Court Location - CourtRefNo

If the [CourtRefNo] Has nothing in it I want to check the QcatRefNo and it it has nothing in it I just want a ' '
If the QcatRefNo has something in it I want to use the QcatLoaction and the QcatRefNo

Now that's the logic, my code is below

IIF(IsNull([CourtRefNo]) Or Len([CourtRefNo]) = 0,

IIF(IsNull([QcatRefNo]) Or Len([QcatRefNo]) = 0," ",
    
"QCAT - " & [QcatLocation] & " - " & [QcatRefNo],

"Court - " & [CourtLocation] & " - " & [CourtRefNo]))

I have spread it out for clarity

Any help would be great

 

[arnelgp]

Swich(IsNull([CourtRefNo] + [QCatRefNo]), Null, IsNull([CourtRefNo]), "QCAT - " & [QCatLocation] & " - " & [QCatRefNo], True, "Court - " & [CourtLocation] & " - " & [CourtRefNo])

 

[mcalex]

To me your IIf looks like:

IIF(
  -- condition
  IsNull([CourtRefNo]) Or Len([CourtRefNo]) = 0,
  -- truepart
  IIF(
      -- condition
      IsNull([QcatRefNo]) Or Len([QcatRefNo]) = 0,
      -- truepart
      " ",
      -- falsepart
      "QCAT - " & [QcatLocation] & " - " & [QcatRefNo],
  
      -- otherpart?
      "Court - " & [CourtLocation] & " - " & [CourtRefNo]
  )
)

I think a closing bracket is in the wrong place.

Following your rules, I think this might work, but it switches the logic around

IIF(
  -- condition
  Not IsNull([CourtRefNo]) Or Len([CourtRefNo]) > 0,
  -- truepart
  "Court - " & [CourtLocation] & " - " & [CourtRefNo]
  -- falsepart
  IIf(
      -- condition
      IsNull([QcatRefNo]) Or Len([QcatRefNo]) = 0,
      -- truepart
      " ",
      -- false part
      "QCAT - " & [QcatLocation] & " - " & [QcatRefNo]
  )
)

hth


mcalex

 

[atrium]

I changed it to mcalex suggestion - still got invalid syntax

IIF(Not IsNull([CourtRefNo]) Or Len([CourtRefNo]) > 0, "Court - " & [CourtLocation] & " - " & [CourtRefNo], IIF(IsNull([QcatRefNo]) Or Len([QcatRefNo]) = 0," ", "QCAT - " [QcatLocation] & " - " & [QcatRefNo]))

 

[CJ_London]

you are missing a & in the final false part

"QCAT - " [QcatLocation]