Combine Fields ( Need to Adjust Code )

[Raj]

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")
Else
strTradeMarks = strTradeMarks & ", " & rs.Fields("trademark")
End If
rs.MoveNext
Loop
If intRecords = 1 Then
strTradeMarks = strTradeMarks & " is a registered trademark of company
" & varProdName
Else
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


i.e.

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.

 

[gllincoln]

Hi Raj,

I looked at this - started to tweak your code to do the thing, but it kept
getting harder.

In my experience - when I start working on a little problem like this - if
it keeps getting harder, I am probably doing it wrong. Maybe not wrong for
you, but wrong for me, the way I think and work and etc.

So I stepped back - looked at the problem again, started writing code while
visualizing myself stepping through the rows and this is what I came up
with. I created your table and named it TEST. This approach seems to work -
but I didn't test it thoroughly. Note there is the main function
myTradeMarks and a sub myNameIs. You could readily include the code of
myNameIs into the main function but this is how i broke it down. The word
wrap is going to be bad - but (hopefully) the line breaks will be reasonably
obvious and you can reconstruct the code in notepad or whatever if it
interests you.

Hope this helps...
Gordon

Public Function myTradeMarks() As String

Dim rs As DAO.Recordset 'the query recordset
Dim intCount As Integer 'company tm count
Dim r As Long 'loop counter
Dim strProd As String 'product name
Dim strCName As String 'company name
Dim strSQL As String 'the query
Dim strText As String 'the current line of text


strSQL = "SELECT Product, Company, Trademark "
strSQL = strSQL & "FROM test "
strSQL = strSQL & "ORDER BY Product, Company, Trademark;"

Set rs = CurrentDb.OpenRecordset(strSQL)

strProd = rs.Fields("Product").Value
strCName = rs.Fields("Company").Value
strText = strProd & " "
intCount = 0

Do While Not rs.EOF
If strProd = rs.Fields("Product").Value Then
If strCName = rs.Fields("Company").Value Then
strText = strText & rs.Fields("Trademark").Value & ", "
intCount = intCount + 1
Else
myTradeMarks = myTradeMarks & Left(strText, Len(strText) -
2) & myNameIs(intCount, strCName)
strText = ""
intCount = 1
strCName = rs.Fields("Company").Value
strText = strText & rs.Fields("Trademark").Value & ", "
End If
Else
myTradeMarks = myTradeMarks & Left(strText, Len(strText) - 2) &
myNameIs(intCount, strCName) & vbCrLf
strProd = rs.Fields("Product").Value
strText = strProd & " "
strCName = rs.Fields("Company").Value
strText = strText & rs.Fields("Trademark").Value & ", "
intCount = 1
End If
rs.MoveNext
Loop
myTradeMarks = myTradeMarks & Left(strText, Len(strText) - 2) &
myNameIs(intCount, strCName)

End Function

Function myNameIs(ByVal i As Integer, ByVal s As String)

If i > 1 Then
myNameIs = " are registered Trademarks of " & s & ". "
Else
myNameIs = " is a registered Trademark of " & s & ". "
End If

End Function

 

[patilrahul78]

Thank you Gllincoln,

This is exactly what I was looking for .

I have removed the line

strText = strProd & " "


to remove product code from the combo text.

Once again

Thank You Very Much
Raj

 

[patilrahul78]

I noticed that In the previous code it was adding a "and" before the
last trademark ( if there are more than one trade mark) and in the new
code it is not putting the "and" before the last trademark.


Can you please adjust the code to keep the "and"

Example

If there is only one trademark it should give something like
"T1 is a registered Trademark of C6"


If there are two trade marks it should give something like
"T1 and T9 are registered Trademarks of C6"


If there are more than two trademarks it should give something like
"T1, T6 and T9 are registered Trademarks of C6"



in the previous code ( see above) it was putting "and" before the last
trademark

Can you please change this to add "and" before the last trademark


Thanks
Raj