Using MS Access/Cont: Function Error


Here is a direct copy/paste of what is in my module:

Public Function fConcatFlds()
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'Code Courtesy of
'Dev Ashish
Function fConcatFld(stTable As String, _
         stForFld As String, _
         stFldToConcat As String, _
         stForFldType As String, _
         vForFldVal As Variant) _
         As String
'Returns mutiple field values for each unique value
'of another field in a single table
'in a semi-colon separated format.
'Usage Examples:
'   ?fConcatFld(("Customers","ContactTitle","CustomerID", _
'          "string","Owner")
'Where  Customers     = The parent Table
'       ContactTitle  = The field whose values to use for lookups
'       CustomerID    = Field name to concatenate
'       string        = DataType of ContactTitle field
'       Owner         = Value on which to return concatenated CustomerID
Dim lodb As Database, lors As Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Const cQ = """"

   On Error GoTo Err_fConcatFld
   lovConcat = Null
   Set lodb = CurrentDb
   loSQL = "SELECT [" & stFldToConcat & "] FROM ["
   loSQL = loSQL & stTable & "] WHERE "
   Select Case stForFldType
       Case "String":
         loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQ
       Case "Long", "Integer", "Double":    'AutoNumber is Type Long
         loSQL = loSQL & "[" & stForFld & "] = " & vForFldVal
       Case Else
         GoTo Err_fConcatFld
   End Select
   Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)
   'Are we sure that duplicates exist in stFldToConcat
   With lors
       If .RecordCount <> 0 Then
         'start concatenating records
         Do While Not .EOF
         lovConcat = lovConcat & lors(stFldToConcat) & "; "
         GoTo Exit_fConcatFld
       End If
   End With
   'That's it... you should have a concatenated string now
   'Just Trim the trailing ;
   fConcatFld = Left(lovConcat, Len(lovConcat) - 2)

   Set lors = Nothing: Set lodb = Nothing
   Exit Function

   MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
   Resume Exit_fConcatFld
End Function

OK, I think I see the problem Delete everything above

Function fConcatFld(stTable As String, _

And change that to Public Function. That should do it.

Then use fConcatFld(....) in your query.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.