Change/make this "Select query " to add same field of same ID - How? 1

[cuok]

Hi fiends!

I have this query that works fine.
======== Query ===================================
SQL = "SELECT ComplicationsRespiratory.Id, ComplicationsRespiratory.HistoryActiveRespProb, ComplicationsRespiratory.DateCheck
FROM ComplicationsRespiratory
WHERE (((ComplicationsRespiratory.Id)=[forms]![frmrashi]![id]))
ORDER BY ComplicationsRespiratory.DateCheck DESC;
"
========== End Query ==================================

"HistoryActiveRespProb" is a MemoField
------------------------------------------------
the query gives result like this:

ID HistoryActiveRespProb (Memo)

1 A
1 B
1 C
1 D
----------------------------------------------------------
What i tried to do with no success is to get from (by?) this query the next result:

ID HistoryActiveRespProb (Memo)

1 A
B
C
D

A,B,C,D IN the SAME FIELD

How can i change/make a query to get (=as a 'chain') ALL "HistoryActiveRespProb" fields gathered into the last or first record or any other way?

Thanks for your help
CUOK

 

[SBendBuckeye]

You can include a function in a query if it is defined as Public in a standard module. Put something like the following in any standard module and modify it until it returns what you need it to when running it from the immediate window (eg ? ConcatenateMemoFields(1), etc.) I put an alias on your table name to try to keep it from wrapping go bad here. I could not test this since I tried to build it based on the SQL you posted above so you may need to tweak it a little bit.

Public Function ConcatenateMemoFields(lngKeyID As Long) As String
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strTemp As String
strSQL = "SELECT CompResp.ID, " _
& "CompResp.HistoryActiveRespProb, " _
& "CompResp.DateCheck " _
& "FROM ComplicationsRespiratory AS CompResp " _
& "WHERE CompResp.ID = " & lngKeyID & " " _
& "ORDER BY CompResp.DateCheck DESC;"
Set rst = CurrentDb.OpenRecordset(strSQL)
With rst
.MoveFirst
strTemp = "ID Date HistoryActiveRespProb (Memo)" & vbCrLf _
& .Fields("ID&quot & vbTab & .Fields("DateCheck&quot & vbTab _
& .Fields("HistoryActiveRespProb&quot & vbCrLf 'Setup first one
.MoveNext 'Start with second record below
Do Until rst.EOF
strTemp = strTemp & vbTab & .Fields("DateCheck&quot & vbTab _
& .Fields("HistoryActiveRespProb&quot & vbCrLf
.MoveNext
Loop
.Close
End With
Set rst = Nothing
ConcatenateMemoFields = strTemp
End Function 'ConcatenateMemoFields

Once you have the function working the way you need it to then modify your above SQL so it only brings back one row with the ID in it and use it in conjunction with the function above. Since you are doing all of the formatting in the function itself, I think the following should work for you. If it doesn't, we may need to convert it into a subquery but that is more work.

"SELECT ConcatenateMemoFields(CompResp.Id) AS ListMemo
FROM ComplicationsRespiratory AS CompResp
WHERE (((CompResp.Id)=[forms]![frmrashi]![id]));"

Good Luck and thanks for the fun!

 

[cuok]

Good morning SBendBuckeye and All!

Thank you very very much!
I waked up 3 minutes ago to see if i got Ans. (now 08:30)

I'm going to do your advice only today evening (working till then).

I have glanced your Ans. and it seems to be work after some modification - to suite my project.

I let you know What i got.
I feel your star is coming!!
Thanks again
CUOK

 

[SBendBuckeye]

Have a great day. I'm going to bed as it is almost 2:00 AM here. I guess I'm a slacker since I'm going to bed at 8:30.

 

[cuok]

Dear SBendBuckeye !


by mistake i put this Q. in this forum, then i got your help that almost have no different than Paul answer
IN thread701-457683 (IN QUERY FORUM)

I REALY BELIEVE THAT YOU HAVE A STAR HONESTLY !

Thank you very very Much!!!
CUOK