title: MS Access VBA - Loop Split Function and Output Either First Value (If Not Null) or Last Value
body: <p>I have a field in my Access database that contains values such as <code>24,25,152</code>, <code>128,152</code>, <code>,113</code>, <code>113</code> and NULLS.</p>
  
  <p>When there is only one value present in the field I would like the first value to be my output (<code>113</code> for <code>,113</code> and <code>113</code>) and when there is more than one value present I would like the last value to be my output (<code>152</code> for <code>24,25,152</code> and <code>128,152</code>).</p>
  
  <p>Right now I have a user-defined function that is invoked by a query that has been hard-coded to account for the correct number of commas/values present in the field. In the future there will be more commas so I would like to account for those and I'd like my output to be in a single column (as opposed to having one column per value after each comma).</p>
  
  <p>Here is VBA code for that user-defined function which came from <a href="https://answers.microsoft.com/en-us/msoffice/forum/all/separate-comma-delimited-field-into-separate/78e97b88-562d-4b39-9706-d633039332f8" rel="nofollow noreferrer">this post</a>.</p>
  
  <pre><code>Function mySplit(sMyText As String, sDelim As String, lIndx As Long) As String
      On Error GoTo Error_Handler
  
      mySplit = Split(sMyText, sDelim)(lIndx)
  
  Error_Handler_Exit:
      On Error Resume Next
      Exit Function
  
  Error_Handler:
      If Err.Number = 9 Then
          mySplit = ""
      Else
          MsgBox "The following error has occured" &amp; vbCrLf &amp; vbCrLf &amp; _
                 "Error Number: " &amp; Err.Number &amp; vbCrLf &amp; _
                 "Error Source: mySplit" &amp; vbCrLf &amp; _
                 "Error Description: " &amp; Err.Description &amp; _
                 Switch(Erl = 0, "", Erl &lt;&gt; 0, vbCrLf &amp; "Line No: " &amp; Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occured!"
      End If
      Resume Error_Handler_Exit
  
  
  
  End Function
  </code></pre>
  
  <p>Here is the query:</p>
  
  <pre><code>SELECT Field, mySplit([field].[table],",",0) AS 1, mySplit([field].[table],",",1) AS 2, mySplit([field].[table],",",2) AS 3, Val(IIf([3]&lt;&gt;"",[3],IIf([2]&lt;&gt;"",[2],IIf([1]&lt;&gt;"",[1])))) AS [Value]
  
  FROM Table;
  </code></pre>
  
  <p>Ideally I'd to have a single field that looks like the "Value" field (outlined in green) in the image below.</p>
  
  <p>Right now that value field is a bunch of nested <code>if</code> statements looking at the <code>1</code>, <code>2</code>, and <code>3</code> columns. I know I need to modify this code to count the delimiters and then loop through each delimiter and take either the first value (if there is only one) and the last value (if there is more than one) but I am not sure how to go about achieving that.</p>
  
  <p>Any help will be greatly appreciated.</p>
  
  <p><a href="https://i.stack.imgur.com/ham00.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ham00.png" alt=""></a></p>
  
  <p><strong><em>EDIT</em></strong></p>
  
  <p><a href="https://i.stack.imgur.com/KRPeC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KRPeC.png" alt="enter image description here"></a></p>