Strip data from Concatenated string 1

[jonohara999]

HI There

I've used the concatenate code, from the FAQ in this forum, to create a string of related products on my shopping cart. This works well, but i'd like to strip out one of the values in the string which is a duplicate. So i currently return data like this...

productid related productids (concatenated)
1 1,2,3,4,5
2 1,2,3,4,5
3 1,2,3,4,5
etc

and i'd like data like this...

productid related products
1 2,3,4,5
2 1,3,4,5
3 1,2,4,5
etc

Is that possible?

Thanks

Jon

 

[Remou]

Replace may suit:

Replace([Related ProductIDs],[ProductID] & ",","")
Replace([Related ProductIDs],[ProductID],"")

Or you may wish to modify the concatenation code to skip the value of [productid].

 

[dhookom]

You could modify the SQL statement in the function to not include the ProductID of the current record. If you post the SQL, someone could provide more specific assistance.

Concatenate("SELECT ProductID FROM tblProducts WHERE ProdCatID = " & ProdCatID & " AND ProductID <>" & ProductID & " ORDER BY ProductID")

Duane
Hook'D on Access
MS Access MVP

 

[jonohara999]

Thanks that's great. The sql i'm currently using is

Concatenate("SELECT intcatalogid FROM tblsofabedsCONCAT WHERE categoryidsb =" & [categoryidsb]) AS relatedproducts
FROM tblSofaBedsCONCAT;

Where intcatalogid is the 'productid'

Jon

 

[dhookom]

Try the following which assumes intcatalogid is numeric:

Concatenate("SELECT intcatalogid FROM tblsofabedsCONCAT WHERE categoryidsb =" & [categoryidsb] & " AND intcatalogid <> " & intcatalogid) AS relatedproducts
FROM tblSofaBedsCONCAT;

Duane
Hook'D on Access
MS Access MVP

 

[jonohara999]

Fantastic thanks very much for all your help.

Jon