Concatenate function truncates the text

[garyholc]

Hi

Im trying to use Duane's concatenate function in a query. Basically I have two tables, one contains all the main records, the other table contains all the comments people have added relating to that table. The comment field in the comments table is a memo field.

In the query, im simply looking for it to combine all the comments into one field so it can be exported to Excel.

The function works ok by concatenating the Date, the area the comment relates to and the comment seperated by a comma - BUT, im hitting the problem that its truncating the text and I cant find a way around it. There is no Group By clause in the query.

The function script is as follows:

----
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ",") _
As String
' open the recordset using the current connection
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

' with the record set, go through each record until end of file and string the two fields together using
' the delimter specified above (pstrDelim)
Dim strConcat As String
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With

' close the recordset
Set rs = Nothing
' output string without initial seperator
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If

Concatenate = strConcat
End Function
-----

The Query in access is as follows:

SELECT T_RFC_Main_Data.Record_Number, T_RFC_Main_Data.RFC_Number, T_RFC_Main_Data.Description, Concatenate("SELECT Date_added & ' -(' & Relates_to & ') - ' & Comment FROM T_Comments WHERE link_to_rfc_rec_no =" & [t_rfc_main_data.record_number]) AS Commentis
FROM T_RFC_Main_Data;


Could anyone offer a work around for this?

Thanks
Gary

 

[garyholc]

Hi Bob

Thanks for that reply. Ok I have used a LEFT JOIN linking the main data to the comments table, and I removed the WHERE statement from the Concatenate in the query.

However, I now get multiple rows, because one record in the main table may have 20-30 rows in the comments table.

 

[garyholc]

Hi Bob

No, I want every comment that relates to the record number of the main table to be concatenated into the one field.

So for example:

Main table:

Rec Num
======
1
2
3
4
5

Comments Table

Links to rec num Date Comment
===========================
1 - 10/10/2010 - Change accepted
1 - 11/10/2010 - Change authorised
1 - 12/10/2010 - Change completed
2
2
3
3
3

etc....

So when it comes to export, I want one line for Record number 1 from the main table, and on the end, a field called comments, which will show those 3 records above all in the one field.... and the same for record 2, 3 etc.

The concatenate script I originally posted does work, but it truncates the text.

Thanks
Gary

 

[garyholc]

Hi

Yeah I tried this and still get the same result, it truncates the text. The comments field in the comments table is of Memo type, but im thinking that Access is truncating after 255 characters which seems to be documented all over the place. Ive tried removing the "string" from the variable int he function script but it still does the same. Maybe its something I cannot get around.

If so, could you think of any other way I could do this? Was thinking maybe some script that would go through the comments table and put all the comments for the same record number into one field perhaps in a new table or something?

Thanks for your help, is much appreciated.

Gary

 

[garyholc]

Hi Bob

Yes I agree, ive never had to do it either but the user is demanding a way of exporting all the comments into one cell in Excel, which in my eyes kinda defeats the point of having the database in the first place!

Anyway, thank you for your help!

Gary

 

[DHudson]

Access 2010: I have 6 fields, each with <255 characters. I'm trying to concatenate them all into one summary field. When I try and set up a calculated field - it does not allow me to set up as a memo field - only a text field - so it truncates anything >255 in the summary.

My end result will be an excel report - so is my only/best option to just export each of the fields and concatenate in Excel? This is a recurring report, so it's rather annoying. I tried using the report function in Access, which was a joke compared to Excel's capabilities, and truncated anyway.