Concatenated list of sub-record values--cannot make into memo

[morriand]

Hello,

I have a database, where I am trying to concatenate sub-record values into one cell so that when I use mail merge, I can actually have a printout of all the subvalues on one record. I am sure my problem is easy to solve but I am a physician who ended up having to teach himself computers over the past 4 years rather than actually be trained as a programmer:

My SQL code is:
SELECT tDemographics.*, fConcatChild("tChronic Problems","MRN","ChronicProblem","String",[MRN]) AS [Chronic Problem Summary]
FROM tDemographics
WHERE (((tDemographics.MRN) = [MRN]));

using the following module:

Option Compare Database
Option Explicit
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String
'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String", "Memo", "Text":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
'Case Else
GoTo Err_fConcatChild '
End Select

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & Chr(13)
.MoveNext
Loop
End If
End With

fConcatChild = Left(varConcat, Len(varConcat) - 1)

Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function

My problem is that I only seem to be able to output a concatenated string of 255 characters (?text) whereas I need it to output up to 2000 characters. What can I change in the code to fix this problem?

Andrew

 

[Alexandre]

Strings can hold up to 2^31 characters, so this should not be the problem. Tell us more about how you are using/displayin the result.

Alex

 

[morriand]

Well, I first make a concatendated field, so that I have a chronic problem and the associated (cpHistory, cpTherapeutics)details in one string:

SELECT [tChronic Problems].*, (([tChronic Problems].[ChronicProblem]) & ":" & ([tChronic Problems].[cpHistory]) & " " & ([tChronic Problems].[cpTherapeuticsCurrent])) AS DetailedChronicProblem
FROM [tChronic Problems];

I then use the module with the following SQL code:

SELECT tDemographics.*, fConcatChild("qryDetailedChronicProblems","MRN","DetailedChronicProblem","String",[MRN]) AS [Chronic Problem Summary]
FROM tDemographics
WHERE (((tDemographics.MRN) = [MRN]));

The query that get processed with this sequel code is fine until, for each record, 255 characters are reported, after which I see nonsense:

Atrial fibrillation:Had ventricular tachycardia in 1996, and subsequently had an Automatic Implantable Cardiac Defibrillator placed. This has been well functioning, although he has now had problems with atrial fibrillation, necessitating warfarin anticoa

When I merge with word, I end up having this field looping a few times. I cannot tell you the endless hours of frustration this has caused. Is it something in how I specify the format of my query? I thought it was a mail merge problem, but the problem pops up before I even use Word.

Would love some help with this.

Andrew

 

[Alexandre]

Andrew,

I ve just tried using sample data, and I get the expected results without problem (with strings of 500-800 characters). It is not easy to tell where your problem lies. Eventually zip and send me your DB (if you want, change the patients names to numbers or whatever)

Alex