IIF statement in Access corrupting where values later in hierarchy overlap

Question

I am in part refining an earlier question to try to ask something tangible here. To frame the issue, I have a somewhat large data set (ca. 250k rows) where I need to transform a given concatenated field into an output for each line item based on whether or not a certain string of text was found within the field. This has been managed in Excel previously (using IF(IFERROR(FIND())), but the formula is too taxing on Excel and I need to move the entire process to Access to better automate it. The end goal is to generate a single table that can paste into an Excel model which then refreshes off the revised date.

Existing SQL:

SELECT my_field_1 ,
my_field_2,
my_field_3,
IIF(Active = 'Yes' AND Issue IS NULL,'OK',
IIF(Issue LIKE '123','123',
IIF(Issue LIKE'234','234',
IIF(Issue LIKE '345','345',

//* Etc going down to 14 nested IIF statements in the above format and 
//* returning NULL where conditions are not met. I then roll up the 3 issue
//* sets as follows

IIF(IssueSet1 IS NOT NULL, IssueSet1,
IIF(IssueSet2 IS NOT NULL, IssueSet2,
IIF(IssueSet3 IS NOT NULL, IssueSet3,
'Check for errors')))

Desired output

',123,234' should return '123' in a successful query output into column RollUp.

The hierarchy as it stands is split into 2 sets of 14 nested IIF statements in the above format with a 3rd set of 4 nested IIF statements. I then roll up all the IIF statements as above. Unfortunately, Access is returning ca. 20k NULL items where an earlier IIF is being triggered before a later IIF on the same row, e.g. 1 row meets conditions for 2 of the IIFs, which is inevitable as I need to use this hierarchy to 'prioritise' the more pressing issues further up in the hierarchy.

Problems:

Where the hierarchy triggers '123' first but then '234' follows on the same line item, it returns NULL instead of returning 123. Is there any way I can get this to stop occurring so anything further up in a hierarchy trumps anything below it? This would work fine in a CASE statement if I could do this on a platform other than Access which is very frustrating.
The existing format is clunky and I would prefer to use a lookup column and a permanent table but I cannot figure out how to return only the value I need (e.g. '123') and not the entire field (e.g. ',123,234,345,456')
I need to ultimately generate a summary output that can easily be copy and pasted into quite an Excel model (or input automatically via VBA) which gives Issue ID (IIF generated), Category (already exists), Sub-Category (Already exists), Count (sum grouped on Issue ID, Category and Sub-Category which I can do myself if I sort this IIF problem).

Many thanks and I hope this clarifies what I am trying to do.

J

I'm not sure I completely follow, but your Likes work as equality operators because you are not using any wildcards. If you meant to say "begins with 123," that's '123*'. — GSerg
I do need them to return equality operators as the mocked up example of '123' could occur anywhere in the string. If '123' occurs anywhere in the string, I need to return '123' even if '234' is also present. Right now, where '234' is also present it is returning NULL. Does that help? — JPsql
If 123 can occur anywhere in the string, an equality operator will most certainly not find anything. "Anywhere in the string" is '*123*'. — GSerg
Well this is embarassing. Spot on! Completely sorted the problem. Sorry, I feel silly for asking that now. Completely missed that. Thank you. — JPsql
Maybe you could reduce the number of IIFs, if it were known that the original string always follows a certain format. If this is the case, please share that format. — trincot

GSerg GSerg · Accepted Answer · 2016-01-24T13:08:40

Issue LIKE '123' is equivalent to Issue = '123' because the pattern does not contain any wildcards.

If you meant to say "where Issue contains '123'," that would be Issue LIKE '*123*'.
However you must then make sure you don't accidentally specify a substring of a longer possible value. E.g. if the list of values in Issue contains '...,987123,...', you probably don't want to match 123 in that, but it will match.

The best approach is to parse the Issue into an array of some sort and see if it contains a value. E.g. by writing a VBA function and using it in the query:

Option Compare Database

Public Function ContainsInList(ByVal CommaSeparatedList As Variant, ByVal Value As Variant) As Boolean
  If IsNull(CommaSeparatedList) Then Exit Function
  If IsEmpty(CommaSeparatedList) Then Exit Function
  If IsNull(Value) Then Exit Function
  If IsEmpty(Value) Then Exit Function
  If Len(CommaSeparatedList) = 0 Then Exit Function
  If Len(Value) = 0 Then Exit Function

  ContainsInList = InArray(Split(CommaSeparatedList, ","), Value)
End Function

Public Function InArray(ByRef Arr As Variant, ByVal Value As Variant) As Boolean
  Dim i As Long

  For i = LBound(Arr) To UBound(Arr)
    If Arr(i) = Value Then
      InArray = True
      Exit Function
    End If
  Next
End Function

SELECT
  IIF(Active = 'Yes' AND Issue IS NULL,'OK',
  IIF(ContainsInList(Issue, '123'), '123',
  IIF(ContainsInList(Issue, '234'), '234', ...

At which point it is not difficult to create a lookup table for your 123s, e.g.:

priority int not null,
value varchar(50) not null

and use it instead of the nested IFs:

SELECT
  IIF(Active = 'Yes' AND Issue IS NULL, 'OK',
  NZ((select top 1 [123s].value from [123s] where ContainsInList(Issue, [123s].value) order by [123s].priority), Issue))

IIF statement in Access corrupting where values later in hierarchy overlap

1 Answers