By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Concatenate all records in 1 Field and output in Text file

P: 5
Hi Everybody,

I need to concatenate all records on one field as one line.

Example: Field 1 has 50 records, record1, record2 to record50. I need to display an output in Text that read as:

record1,record2,record3,record4,record5,record6..r ecord50

No space and no line breaks.

I tried creating a report and replace vbCrLf with "" and then OutputTo text file
but I can't have it to have only only 1 line with now line breaks.

All help is appreciated.

thanks.
Jan 31 '08 #1
Share this Question
Share on Google+
1 Reply


ADezii
Expert 5K+
P: 8,679
Hi Everybody,

I need to concatenate all records on one field as one line.

Example: Field 1 has 50 records, record1, record2 to record50. I need to display an output in Text that read as:

record1,record2,record3,record4,record5,record6..r ecord50

No space and no line breaks.

I tried creating a report and replace vbCrLf with "" and then OutputTo text file
but I can't have it to have only only 1 line with now line breaks.

All help is appreciated.

thanks.
Assuming your Table Name is tblTest, I created this code for a series of 50 Numbers (50 Records) in a Field named Field1:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As Recordset, strBuild As String
  2.  
  3. Set MyDB = CurrentDb
  4. Set MyRS = MyDB.OpenRecordset("tblTest", dbOpenForwardOnly)
  5.  
  6. Do While Not MyRS.EOF
  7.   strBuild = strBuild & MyRS![Field1] & ","
  8.   MyRS.MoveNext
  9. Loop
  10.  
  11. strBuild = Left$(strBuild, Len(strBuild) - 1)    'remove trailing ,
  12.  
  13. Open "C:\BigString.txt" For Output As #1
  14. Print #1, strBuild
  15.  
  16. MyRS.Close
  17. Set MyRS = Nothing
  18.  
  19. Close #1
OUTPUT: (won't Format correctly)
Expand|Select|Wrap|Line Numbers
  1. 65328,98763,104084,105688,106755,109410,109471,113047,113062,115333,118480,118487,118509,120426,123309,126184,129286,129342,129362,129369,135984,136274,136284,136306,138280,138286,138293,138328,138344,138349,138357,138358,138365,140432,140435,140459,140468,140479,140486,143019,143027,143043,144793,144794,144798,144803,144810,144814,144835,146336
Feb 1 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.