• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • 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 .....
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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