Access里自定义类似MySQL group_concat 的函数

aa0660

　　MySQL里的group_concat很方便，MS-Access里没有。网上找到一个：http://access.mvps.org/access/modules/mdl0008.htm
　　
Concatenate fields in same tableAuthor(s) Dev Ashish (Q)    I need to concatenate a field in the format "Value1; Value2; Value3" etc. for each unique value of another field in the same table.  How can I do this?(A)    Using the fConcatFld function,  in the Northwind database, the following query should return a concatenated list of all CustomerIDs if you group by ContactTitle. SELECT ContactTitle, fConcatFld("Customers","ContactTitle","CustomerID","string",[ContactTitle]) AS Customers FROM Customers GROUP BY ContactTitle;'************ Code Start **********'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 RecordsetDim lovConcat As Variant, loCriteria As StringDim loSQL As StringConst cQ = """"On Error GoTo Err_fConcatFldlovConcat = NullSet lodb = CurrentDbloSQL = "SELECT [" & stFldToConcat & "] FROM ["loSQL = loSQL & stTable & "] WHERE "Select Case stForFldTypeCase "String":loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQCase "Long", "Integer", "Double":    'AutoNumber is Type LongloSQL = loSQL & "[" & stForFld & "] = " & vForFldValCase ElseGoTo Err_fConcatFldEnd SelectSet lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)'Are we sure that duplicates exist in stFldToConcatWith lorsIf .RecordCount <> 0 Then'start concatenating recordsDo While Not .EOFlovConcat = lovConcat & lors(stFldToConcat) & "; ".MoveNextLoopElseGoTo Exit_fConcatFldEnd IfEnd With'That's it... you should have a concatenated string now'Just Trim the trailing ;fConcatFld = Left(lovConcat, Len(lovConcat) - 2)Exit_fConcatFld:Set lors = Nothing: Set lodb = NothingExit FunctionErr_fConcatFld:MsgBox "Error#: " & Err.Number & vbCrLf & Err.DescriptionResume Exit_fConcatFldEnd Function'************ Code End **********