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

Posted on 2008-11-06
Last Modified: 2013-11-27
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

Question by:jimbofish8
    1 Comment
    LVL 26

    Accepted Solution

    change ykour select  statement like this using the  DISTINCT  keyword

    SELECT DISTINCT Prod_Type FROM Order_Detail WHERE .....

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now