CombineChildRecords Function Truncates the field at 255 chars.

[bkf7911]

I am trying to use the CombineChildRecords Function to concatenate the many results for one row. Unfortunately, the results are truncating after 255 characters. HOw can i get Access to look at this as a LongText/Memo field and stop truncating?

*****************************************************

Function CombineChildRecords(strTblQryIn As String, _
strFieldNameIn As String, strLinkChildFieldNameIn As String, _
varPKVvalue As Variant, Optional strDelimiter) As Variant

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim varResult As Variant

Set db = CurrentDb
Set qd = db.CreateQueryDef("")

If IsMissing(strDelimiter) Then strDelimiter = "; "
strSQL = "SELECT [" & strFieldNameIn & "] FROM [" & strTblQryIn & "]"
qd.SQL = strSQL & " WHERE [" & strLinkChildFieldNameIn & "] = [ParamIn]"
qd.Parameters("ParamIn").Value = varPKVvalue

Set rs = qd.OpenRecordset()

Do Until rs.EOF
varResult = varResult & rs.Fields(strFieldNameIn).Value & strDelimiter
rs.MoveNext
Loop

rs.Close

If Len(varResult) > 0 Then varResult = Left$(varResult, _
Len(varResult) - 1)

CombineChildRecords = varResult

Set rs = Nothing
Set qd = Nothing
Set db = Nothing
End Function

 

[dhookom]

Where is the value truncated? Where are you displaying the value? What happens if you add a line to display the value in the debug window?

CombineChildRecords = varResult
debug.Print varResult

Duane
Hook'D on Access
MS Access MVP

 

[bkf7911]

That statement didn't make a difference. I call the function through the following query. The ProductsList field is short text and i want it as memo.

SELECT CombineChildRecords("tblAccount","Account","Filler",[Filler]," ") AS ProductsList, tblAccount.Filler
FROM tblAccount
GROUP BY CombineChildRecords("tblAccount","Account","Filler",[Filler]," "), tblAccount.Filler;

 

[PHV]

The culprit is here: GROUP BY

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I expect you can create a totals query [qgrpFiller] first:

SELECT DISTINCT Filler
FROM tblAccount

Then create a query to pull the list:

SELECT CombineChildRecords("tblAccount","Account","Filler",[Filler]," ") AS ProductsList, Filler
 FROM qgrpFiller

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

You may also consider a single query like this:

SELECT CombineChildRecords("tblAccount","Account","Filler",A.Filler," ") AS ProductsList, A.Filler
FROM tblAccount A INNER JOIN (
SELECT DISTINCT Filler FROM tblAccount
) D ON A.Filler = D.Filler

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bkf7911]

The single query worked perfectly... thanks much!