SQL Select Distinct Text Fields and Order By - in Excel VBA

[Mav3000]

Hi All,

I've an Excel application which queries a database table, and uses the results to populate a combo box.

However, the table has lots of duplicate values in it - I'd like one of each to appear only, hence a SELECT DISTINCT requirement. I'd also like to sort them in ascending order, so an ORDER BY clause is also needed.

However, the fields are text fields, and when I try to do this, I get the error that I can't do DISTINCT or ORDER BY on text fields.

How can I get around this in order to remove the duplicates and order them alphabetically? This is in VBA so as I understand it, it all needs to be done in a single SELECT statement to return the data.

My query is created dynamically, but essentially looks like this (in duplicated, unordered form):

SELECT 
  tblMobPageDictionary.Table_name,
  tblMobPageDictionary.Field_name,
  tblMobPageDictionary.Label
FROM 
  tblMobPageDictionary

 

[SkipVought]

Hi,

I have never seen this before, as I often query using Distinct on text fields.

Try returning your resultset to a sheet, where you could then point your combo to that source to query as desired.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mav3000]

Thanks Skip, That is the approach I am trying at the moment.

I'm putting the recordset onto a worksheet and using an advanced filter to filter the cells specifying unique records only.

I'll then sort it, and set it as the row source on the combo box.

 

[SkipVought]

This query to an Oracle table executes...

SELECT DISTINCT COST_CENTERS_MM.ORGANIZATION, COST_CENTERS_MM.MANAGER, COST_CENTERS_MM.RESOURCE_ANALYST
FROM FPRPTSAR.COST_CENTERS_MM
ORDER BY 1, 2, 3

and this from an Access db

SELECT DISTINCT `Item Info`.Lot, `Item Info`.Cat, `Item Info`.Brand
FROM `Item Info`
ORDER BY 3, 2, 1

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

It would help to know what the data source is. If it is SQL Server then "Text" suggests a memo type field. I would try something like:

SELECT DISTINCT
  Left(Table_name,100) as TableName,
  Left(Field_name,100) as FieldName,
  Left(Label, 100) as Label
FROM 
  tblMobPageDictionary
ORDER BY 1,2,3

Duane
Hook'D on Access
MS Access MVP

 

[Mav3000]

Sorry guys, it's an SQL database, connecting via ADO. I'm unsure of the exact field types as I can't access the DB directly - only via ADO and Excel.

At the moment I have my function pulling the fields into a worksheet, then removing duplicates and sorting the remainder, then loading them into a combo box.

Not ideal, but it works well enough.