Get unique values to a array variable from Acces Table field

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?

Answer 1

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)