title: MS Access: ConcatRelated function works with source table but not with query
body: <p>I use ConcatRelated function (made by <a href="http://allenbrowne.com/func-concat.html" rel="nofollow noreferrer">Allen Browne</a>) to merge string values from several rows in the MainTable, grouped by CategoryNumber:</p>
  
  <pre><code>ConcatRelated("[TextField]", "[MainTable]", "[CategoryNumber] = " &amp; [CategoryNumber])
  </code></pre>
  
  <p>In that scenario, function works perfectly. However, I need to merge rows with only some of the categories. I store these selected categories in the Table2. I made Query1 that connects Table2 with MainTable through Tag field.</p>
  
  <pre><code>SELECT MainTable.CategoryNumber, MainTable.TextField
  FROM Table2 INNER JOIN MainTable ON Table2.Tag = MainTable.ConnectedTag;
  </code></pre>
  
  <p>Now I have only selected rows I want to use with Concat function. I try to use it in the same way as previous:</p>
  
  <pre><code>ConcatRelated("[TextField]", "[Query1]", "[CategoryNumber] = " &amp; [CategoryNumber])
  </code></pre>
  
  <p>Then occurs <strong>Error 3061: too few parameters. Expected 1.</strong></p>
  
  <p>I also try to use Concat as the event procedure in the form. 
  In result i see <strong>Run-time error '2465' can't find the field '|1'</strong></p>
  
  <p>ConcatRelated module looks like this and, as mentioned before, it works just fine in many other cases:</p>
  
  <pre><code>Public Function ConcatRelated(strField As String, _
  strTable As String, _
  Optional strWhere As String, _
  Optional strOrderBy As String, _
  Optional strSeparator = ", ") As Variant
  On Error GoTo Err_Handler
  Dim rs As DAO.Recordset
  Dim rsMV As DAO.Recordset
  Dim strSQL As String 
  Dim strOut As String
  Dim lngLen As Long
  Dim bIsMultiValue As Boolean
  
  ConcatRelated = Null
  
  strSQL = "SELECT " &amp; strField &amp; " FROM " &amp; strTable
  If strWhere &lt;&gt; vbNullString Then
      strSQL = strSQL &amp; " WHERE " &amp; strWhere
  End If
  If strOrderBy &lt;&gt; vbNullString Then
      strSQL = strSQL &amp; " ORDER BY " &amp; strOrderBy
  End If
  Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
  
  bIsMultiValue = (rs(0).Type &gt; 100)
  
  Do While Not rs.EOF
      If bIsMultiValue Then
          'For multi-valued field, loop through the values
          Set rsMV = rs(0).Value
          Do While Not rsMV.EOF
              If Not IsNull(rsMV(0)) Then
                  strOut = strOut &amp; rsMV(0) &amp; strSeparator
              End If
              rsMV.MoveNext
          Loop
          Set rsMV = Nothing
      ElseIf Not IsNull(rs(0)) Then
          strOut = strOut &amp; rs(0) &amp; strSeparator
      End If
      rs.MoveNext
  Loop
  rs.Close
  
  lngLen = Len(strOut) - Len(strSeparator)
  If lngLen &gt; 0 Then
      ConcatRelated = Left(strOut, lngLen)
  End If
  
  Exit_Handler:
  Set rsMV = Nothing
  Set rs = Nothing
  Exit Function
  
  Err_Handler:
  MsgBox "Error " &amp; Err.Number &amp; ": " &amp; Err.Description, vbExclamation, "ConcatRelated()"
  Resume Exit_Handler
  End Function
  </code></pre>
  
  <p>I use Access 2013 with SQL server. What I do wrong?</p>