mysql concat自定义_Access里自定义类似MySQL group_concat 的函数

Concatenate fields in same table

Author(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 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) & "; "

.MoveNext

Loop

Else

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)

Exit_fConcatFld:

Set lors = Nothing: Set lodb = Nothing

Exit Function

Err_fConcatFld:

MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description

Resume Exit_fConcatFld

End Function

'************ Code End **********

分享到：

2011-09-21 16:32

浏览 413

评论