Get unique values to a array variable from Acces Table field RRS feed

  • Question

  • Hello,

    I am struggling to find the right code to perform the following task:

    I have a field "ProductNumber" in an Access Table (which is a timestamp based record table). I get data to this table from hundreds of text files. So data in Access table is dynamic.

    "ProductNumber" field contains Integer product numbers (there are 10000 records as a minimum).

    However, it might be only one or few unique products in the whole table (it means that there are lets say 2000 records of product #2, 6000 records of product #8 and the rest 2000 records of product #1).

    I would like to make a VBA function that quickly and without looping through the entire field would output array with 3 numbers in this case: 2, 8, 1.

    Having an array with unique numbers I could then use SQL to built a query that would have that particular array as s criteria for data filtering.

    Any suggestions?

    Friday, October 21, 2016 9:27 AM

All replies

  • The query

    SELECT DISTINCT ProductNumber FROM NameOfTheTable

    will return the unique product numbers. You can use this as a subquery in other queries, or you can open a recordset (DAO or ADODB) on this SQL and use it any way you want.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Chenchen Li Wednesday, November 2, 2016 1:03 PM
    Friday, October 21, 2016 9:59 AM