• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

Using a recordset to display unique values within a field in summary

I'm using a function to concatenate values (separated by a comma) of a field in a summarized data set. I have a data set that lists the types of items (eg. B, P, F, D) associated with an order.

Order_ID     Prod_Type
A11            B
A11            P
A11            P
A11            D

Currently, the function I am using will return "B, P, P, D" for A11. I want it however to identify the unique values and return "B,P,D" instead. How can I modify my code to do this?

Function Route(ORD_Str) As String
Dim db As DAO.Database
Dim strSQL As String
Dim rst As DAO.Recordset
Dim PTypes As String
strSQL = "SELECT Prod_Type FROM Order_Detail WHERE Ord_Id='" & 	ORD_Str & "' ORDER BY req_dt desc"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rst.EOF
    PTypes = PTypes & ", " & rst!Prod_Type
Route = Right(PTypes, Len(PTypes) - 2)
End Function

Open in new window

1 Solution
change ykour select  statement like this using the  DISTINCT  keyword

SELECT DISTINCT Prod_Type FROM Order_Detail WHERE .....

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now