Beginner needs help :) MS Access query - how to format data lines into a paragraph

[Jeff123]

I have this query:

SELECT TOP 5 Replace([TXT],"Member","Antonio") AS Expr1
FROM [ADHD/ODD]
ORDER BY Rnd(Int(Now()*id)-Now()*id);

This query selects five items from a table at random, and list these items with a name change (changes the word "member" to a different name).
The query outputs in this format (single spaced):

Antonio eats apples.
Antonio drinks milk.


Antonio watches TV.

I would like for the query (or report) to output like this:

Antonio eats apples. Antonio drinks milk. Antonio watches TV.

I then make a report from this query. I am unsure if the query should be changed, or if there is certain property in the report that should be changed.
I have honestly searched the web, but have no clue. I searched on how to use concatenate, but cannot figure it out. Any help would be GREATLY appreciated
Thanks, Jeff

[ranman256]

this function will collect all the values in the field for all records, and produce 1 string.
use it in a text box , or query:

function CollectMemoFlds()
dim sSql as string, sTxt as string
dim rst

sSql = "select * from table"
Set rst = currentdb.OpenRecordset(sSql)
With rst
  While Not .EOF
      sTxt = sTxt & " " & .Fields("MyField").Value & ""
     
     .MoveNext
  Wend
End With


CollectMemoFlds = sTxt
set rst = nothing
end function

[Jeff123]

this function will collect all the values in the field for all records, and produce 1 string.
use it in a text box , or query:

function CollectMemoFlds()
dim sSql as string, sTxt as string
dim rst

sSql = "select * from table"
Set rst = currentdb.OpenRecordset(sSql)
With rst
  While Not .EOF
      sTxt = sTxt & " " & .Fields("MyField").Value & ""
     
     .MoveNext
  Wend
End With


CollectMemoFlds = sTxt
set rst = nothing
end function

Hello, thanks for your help. I tried working with what you typed, but I'm getting stumped on how to insert that into my query. The query is:

SELECT TOP 5 Replace([TXT],"Member","Antonio") AS Expr1
FROM [ADHD/ODD]
ORDER BY Rnd(Int(Now()*id)-Now()*id);

[ranman256]

you need to build the sql for what you want in the code,


then the query uses:
select CollectMemoFlds([keyFld]) from table


if you want it for 1 key ,we need to alter the code to:

function CollectMemoFlds(pvKey)
dim sSql as string, sTxt as string
dim rst


sSql = "select * from table where [key]=" & pvKey
Set rst = currentdb.OpenRecordset(sSql)
With rst
  While Not .EOF
      sTxt = sTxt & " " & .Fields("MyField").Value & ""
     
     .MoveNext
  Wend
End With


CollectMemoFlds = sTxt
set rst = nothing
end function

[Jeff123]

ok, I will be working on that. Thanks for your time!