Concatenate records of same field (1 Viewer)

[georg0307]

Dear all,
I have a table with filed "Shipments", with i.e. the following records:

1234567
5432188
2345678
5555555

in need in a textbox in the report that shows the data concatenated: 1234567_5432188_2345678_5555555

And in second moment name a PDF output with this text:

1234567_5432188_2345678_5555555.pdf

Could you please help?

Thanks in advance,

Georg

 

[arnelgp]

you can open a recordset from your table and loop through it:

public function concatField(byval tableName as string, byval fldName as string, optional byval criteria as string="(1=1)") as string
dim sRet as string
with currentdb.openrecordset("select [" & fldName & "] from [" & tableName & "] where " & criteria & ";", dbOpensnapshot, dbReadonly)
    if not (.bof and .eof) then
        .movefirst
    end if
    do until .eof
        if not isnull(.fields(0)) then
            sRet = sRet & .fields(0) & "_"
        end if
        .movenext
    loop
end with
if len(sRet)<>0 then
    sRet = Left$(sRet, Len(sRet)-1)
end if
concatField = sRet
end function

on an unbound textbox on Report, set its ControlSource:

=concatField("theTableName","theFieldName")

For the output (pdf)

docmd.OutputTo acOutputReport,"yourReportName",acformatpdf, "pathOfOutput\" & me.unboundtextboxName & ".pdf"

 

[Gasman]

Also theDBGuy has a function SimpleCSV as does Allen Browne Concatenate

 

[georg0307]

hHi,

sure I am wrong, at this point I give you my DB because it doesn't work well. it give me back in the textbox 2 numbers... 13 and 14.

 

[Gasman]

Use the functions, if you cannot code.
theDBguy's is the simplest to use.

 

[arnelgp]

see your report.

 

[georg0307]

Ciao, ok I understood my error thank you very much!!! SOLVED