Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using VBA to create a text string of records (A2k)

    I have a query whose criteria results in a list of names. What I want to do is, on a button click, create a report that will list all the names, separated by a comma, for example, if my query returned:

    NAME
    JOHN
    JOE
    BOB
    FRANK

    I would want my report to show NAME: JOHN, JOE, BOB, FRANK
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to create a text string of records (A2k)

    Hi Jeremy,
    I normally do that by getting a recordset and using a Do Until Loop.
    <pre>With rst
    Do Until .EOF
    strString = strString & [FirstName] & ", "
    .MoveNext
    Loop
    End With
    strString = "NAME: " & Left(strString , Len(strString ) - 2)
    </pre>


    You can then place that into a textbox (or whatever) on your report.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using VBA to create a text string of records (A2k)

    You can also use the Concat function attached to <post#=301393>post 301393</post#>. You must copy that function into a module.

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to create a text string of records (A2k)

    Perfect~! Thanks.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •