แสดงกระทู้
This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.
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 " & strField & " FROM " & strTable
If strWhere <> vbNullString Then
strSql = strSql & " WHERE " & strWhere
End If
If strOrderBy <> vbNullString Then
strSql = strSql & " ORDER BY " & strOrderBy
End If
Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
bIsMultiValue = (rs(0).Type > 100)
Do While Not rs.EOF
If bIsMultiValue Then
Set rsMV = rs(0).Value
Do While Not rsMV.EOF
If Not IsNull(rsMV(0)) Then
strOut = strOut & rsMV(0) & strSeparator
End If
rsMV.MoveNext
Loop
Set rsMV = Nothing
ElseIf Not IsNull(rs(0)) Then
strOut = strOut & rs(0) & strSeparator
End If
rs.MoveNext
Loop
rs.Close
lngLen = Len(strOut) - Len(strSeparator)
If lngLen > 0 Then
ConcatRelated = Left(strOut, lngLen)
End If
Exit_Handler:
Set rsMV = Nothing
Set rs = Nothing
Exit Function
Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
Resume Exit_Handler
End Function
SELECT t1.IDProduct, t1.Qty, [เลขที่เอกสาร] & "(" & [qty] & ")" AS เลขที่
FROM t1;
SELECT Q1.IDProduct, Sum(Q1.Qty) AS ผลรวม, ConcatRelated("เลขที่","Q1","IDProduct = '" & [IDProduct] & "'") AS เลขที่เอกสาร
FROM Q1
GROUP BY Q1.IDProduct, ConcatRelated("เลขที่","Q1","IDProduct = '" & [IDProduct] & "'");SQL = "update M4_sobgen_sci T0 set room_sob = cstr(int(dcount('*', 'M4_sobgen_sci', 'number_sob < ''' & T0.number_sob & '''')/40)+1)"
