Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concantenating Data (Access 2003)

    In a table, I have a customer field and a code field. Each customer has at least one code, but they may have more (the most is currently 7, but it fluctuates). I need to show how many codes each customer has in a report in the format Code1, Code2, Code3, etc. One customer's Code1 is probably not the same as another customer's.

    My thought was to create a second table with two fields - Customer and CodeRange. I set indexes on the Customer field in both tables, and started writing code to populate the CodeRange field. This is what I have so far.

    Dim dbsMYDB As DAO.Database
    Dim rstMYRS1 As DAO.Recordset
    Dim rstMYRS2 As DAO.Recordset

    Dim Code As String

    Set dbsMYDB = DBEngine.Workspaces(0).Databases(0)
    Set rstMYRS1 = dbsMYDB.OpenRecordset("tblCustomers", DB_OPEN_TABLE)
    Set rstMYRS2 = dbsMYDB.OpenRecordset("tblCustomersByCode")

    rstMYRS1.MoveFirst
    rstMYRS2.MoveFirst

    Do Until rstMYRS1.EOF


    rstMYRS2.Index = "2"
    rstMYRS2.Seek "=", rstMYRS1![Company]

    If Not rstMYRS2.NoMatch Then
    SICCode = [rstMYRS2]![Code] & ", "
    End If

    rstMYRS2.MoveNext

    Loop

    I am trying to step through all of the records that match the customer in rstMYRS2 and accumulated their codes, then move to the next customer in rstMYRS1 and find the matches in rstMYRS2.

    Any help you can give me will be appreciated.

    Thanks!

    Nancy

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

    Re: Concantenating Data (Access 2003)

    I wouldn't store the concatenated list in a table, since it is derived information. You can calculate it in a query.

    The attachment to <post#=301,393>post 301,393</post#> contains the code for a function named Concat that you can use to concatenate values into one long string. Copy the function into a standard module. You can then use it in expressions in queries and in the control source of text boxes on forms/reports.

    In your situation, the SQL for the query would be

    SELECT Customer, Concat("tblCustomers", "Code", "Customer=" & Chr(34) & [Customer] & Chr(34)) AS CodeRange
    FROM tblCustomers
    GROUP BY Customer

    I assumed that Customer is a text field. If it is a numeric ID, omit both instances of & Chr(34).

  3. #3
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concantenating Data (Access 2003)

    Hans, you are truly amazing!

    Thank you, thank you, thank you. You have saved me so much time as I can see where I can use this in many other places.

    Nancy

Posting Permissions

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