Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenate fields in a query (Access 2000 Win 2000 SR-1)

    This should be easy but I am having trouble getting started.

    I have a query and want to write the results of one of the query field values into string with a semi-colon seperator and ideally have the results written to, say Notepad so the info can be cut and pasted easily. Using a report would be alright but not optimal.

    any ideas?

    TIA

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

    Re: Concatenate fields in a query (Access 2000 Win 2000 SR-1)

    Do you mean that you want to concatenate the values of a single field for all records returned by a query? For example, you want to concatenate the last names in the following records:

    <table border=1><td>CustomerID</td><td>LastName</td><td>FirstName</td><td align=right>1</td><td>Adams</td><td>John</td><td align=right>2</td><td>Brennan</td><td>Dave</td><td align=right>3</td><td>Collins</td><td>Joan</td><td align=right>4</td><td>Davis</td><td>Miles</td></table>
    resulting in

    Adams;Brennan;Collins;Davis

    Or do you want something else?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate fields in a query (Access 2000 Win 2000 SR-1)

    yes, that is exactly what I want.

    in fooling around in the help file examples I have this:

    <font color=448800>
    Sub OpenRecordsetX(rstCRCGChairs As Recordset)

    Set rstCRCGChairs = CRCGLocalContacts.OpenRecordset("CRCG Chairs")

    ' Enumerate the specified Recordset object.
    With rstCRCGChairs
    Do While Not .EOF
    Debug.Print , .Fields(0), .Fields(1)
    .MoveNext
    Loop
    End With

    End Sub
    </font color=448800>

    which looks like something, but it doesn't run in the module -- i get the run Macros dialog box.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate fields in a query (Access 2000 Win 2000 SR-1)

    Steve,

    One option would be to move the results of your query to Excel (using Office Links) and do the concatenation there.

    If I understand your problem correctly, you want to concatenate the value of one field for all records (rather than all fields for one record). Assuming this is the case, another option would be to create an Access VBA routine that scans your query results and builds the concatenated fields. The routine could look something like this:
    <font face="Georgia">
    <font color=blue>Public Function strConcatenate() As String

    Dim rst As DAO.Recordset</font color=blue>

    <font color=448800>' Access the query results</font color=448800>
    <font color=blue>Set rst = CurrentDb.OpenRecordset("Your Query Name", dbOpenDynaset)

    strConcatenate = ""</font color=blue>

    <font color=448800>' Step through the query result records</font color=448800>
    <font color=blue>Do While Not rst.EOF
    strConcatenate = strConcatenate & rst!FieldToConcatenate & ";"
    rst.MoveNext
    Loop

    Set rst = Nothing</font color=blue>

    <font color=448800>' Remove final semicolon</font color=448800>
    <font color=blue>strConcatenate = Left(strConcatenate, Len(strConcatenate) - 1)

    End Function</font color=blue>
    </font face=georgia>
    You could then just run the function from the VBA Immediate window (type in ?strConcatenate) and copy from there. Or you could create another query that has the SQL code of simply:

    <font color=blue><font face="Georgia">SELECT strConcatenate() AS AllValues;</font face=georgia></font color=blue>

    Then when you run this query, you'll get your concatenated string as the result which you can copy as needed.

    This is a little sketchy; if you need more help, holler.

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

    Re: Concatenate fields in a query (Access 2000 Win 2000 SR-1)

    Put the following function in a standard module:

    Function Concat(aRSet As String, aField As String, Optional aCondition As String, Optional fIncludeNulls As Boolean) As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strRes As String
    If aCondition <> "" Then
    strSQL = " AND (" & aCondition & ")"
    End If
    If Not fIncludeNulls Then
    strSQL = strSQL & " AND ([" & aField & "] IS NOT NULL)"
    End If
    If strSQL <> "" Then
    strSQL = " WHERE" & Mid$(strSQL, 5)
    End If
    strSQL = "SELECT [" & aField & "] FROM [" & aRSet & "]" & strSQL & " ORDER BY [" & aField & "];"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    While Not rst.EOF
    strRes = strRes & ";" & rst(aField)
    rst.MoveNext
    Wend
    If strRes <> "" Then
    strRes = Mid$(strRes, 2)
    End If
    Concat = strRes
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    End Function

    Create a new query based on the existing query, with just one field:

    Together: Concat("QueryName","FieldName")

    where QueryName must be replaced by the name of the existing query and FieldName by the name of the field you want to concatenate. Set the Unique Values property of the new query to Yes. Save this query, then test it. You can use File | Export to export the result to a text file.

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate fields in a query (Access 2000 Win 2000 SR-1)

    You could probably use TransferText to export the query as a delimited file.

    HTH

    Peter

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate fields in a query (Access 2000 Win 2000 SR-1)

    no, that looks perfect. I now have a form in Outlook that displays the various d-list names to add to Outlook as needed.

    Thanks! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

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