Results 1 to 2 of 2

Thread: Combine Fields

  1. #1
    Join Date
    Dec 2006

    Unanswered: Combine Fields

    Public Function getTradeMarks(varProdName As Variant) As String
    Dim rs As DAO.Recordset
    Dim intRecords As Integer
    Dim strTradeMarks As String
    Dim strSql As String

    'strSql = "Select distinct [trademark] from tblTrademarks where [productName] = " & varProdName
    strSql = "Select distinct [trademark] from tblTrademarks where [productName] = '" & varProdName & "'"
    Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)

    Do While Not rs.EOF
    intRecords = intRecords + 1
    If rs.AbsolutePosition = 0 Then
    strTradeMarks = rs.Fields("trademark")
    strTradeMarks = strTradeMarks & ", " & rs.Fields("trademark")
    End If
    If intRecords = 1 Then
    strTradeMarks = strTradeMarks & " is a registered trademark of company " & varProdName
    strTradeMarks = Left(strTradeMarks, InStrRev(strTradeMarks, ",") - 1) & " and" & Mid(strTradeMarks, InStrRev(strTradeMarks, ",") + 1) & " are registered trademarks of " & varProdName
    End If
    getTradeMarks = strTradeMarks
    End Function

    my input:

    ID productName trademark
    1 123 T1
    2 123 T2
    3 123 T3
    4 234 T4
    5 234 T1
    6 123 T6
    7 456 T7
    8 789 T8

    my output:

    productName TradeMarks
    123 T1, T2, T3 and T6 are registered trademarks of 123
    234 T1 and T4 are registered trademarks of 234
    456 T7 is a registered trademark of company 456
    789 T8 is a registered trademark of company 789

    The above code works Excellent.

    I am looking for some code like this , my situation is similar but it has another column

    For example In reference to the same example above I have one more column

    Dose anyone know how to adjust the above code to get the following output


    my Input

    ProductNumber TradeMark CompanyName

    123 T1 C1
    123 T2 C5
    234 T4 C2
    234 T1 C1
    123 T6 C1
    456 T3 C9
    123 T12 C5
    123 T9 C1
    123 T4 C6

    Want out put

    productName TradeMarks
    123 T1, T6 and T9 are registered trademarks of C1. T2 and T12 are registered trademarks of C5. T4 is registered trademark of C6.

    234 T1 is registered trademark of C1. T4 is registered trademark of C2.

    456 T3 is registered trademark of C9.

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    We can help you where you get stuck, but I doubt anyone here is going to just do the work for you.... I guess someone might... but I won't because it makes me think I am doing your job/homework for you.

    If it was me, I'd just scrap the existing code and start again only using fragments and concepts from the existing code. I personally find it quicker than having to modify existing code to suit new requirements... especially if I didn't write the code in the first place.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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