Results 1 to 2 of 2
  1. #1
    sanand is offline Novice
    Windows XP Access 2000
    Join Date
    Jul 2009
    Posts
    2

    getting a comma separated string from a recordset in MS Access

    Hi,



    I have an access query that has 2 columns: CAUSE and count(records) having the respective CAUSE value. I want to add another column that will give me all the ACCOUNT NUMBERS same as the number mentioned in the COUNT column. Also i want all these ACCOUNT NUMBERS to appear in comma separated format on the crystal report.

    Any suggestion as to how we can convert the recordset to a comma separated string.


    Thanks.

  2. #2
    SoftwareMatters is offline Access VBA Developers
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    274
    I would create a function in VBA that loops through the recordset adding the account numbers to a string which will be something like this:
    Code:
    Public Function GetComma(AccNumber)
    dim StrComma as String
    StrComma 
    ""
    set rs.recordset("SELECT AccountNumber FROM Accounts WHERE AccountNumber = " AccNumber ";")
    if 
    rs.recordcount <> 0 then
    rs
    .movefirst
    do until rs.eof
    StrComma 
    StrComma rs!AccountNumber ","
    rs.movenext
    loop
    end 
    if
    rs.close
    GetComma 
    Left(StrComma Len(StrComma ) - 1)
    End Function 

Please reply to this thread with any new information or opinions.

Similar Threads

  1. ADO.Recordset - Write
    By Marvin in forum Programming
    Replies: 0
    Last Post: 07-17-2009, 07:56 AM
  2. Import XML String into Access table
    By chrisjohns in forum Programming
    Replies: 0
    Last Post: 04-16-2009, 01:47 AM
  3. Access 2003 returns empty recordset
    By Leelers in forum Queries
    Replies: 0
    Last Post: 03-20-2009, 11:11 AM
  4. Create a recordset for a report
    By vjboaz in forum Programming
    Replies: 0
    Last Post: 11-18-2008, 03:28 PM
  5. Add new Recordset using Recorset by Query
    By gailoni in forum Programming
    Replies: 1
    Last Post: 10-22-2008, 11:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums