Unique Values from Access - Very difficult!

[gatarossi]

Dear all,

I'm trying to create a validation list with data from access. The code
below already do it, but I have a big problem: in the field of access
database I can have duplicate data...

table: period

fields
year_month year_quarter year
200701 200701 2007
200702 200701 2007
200703 200701 2007
200704 200702 2007
..
..
..
And the code below brings duplicate data. How can I solve this
problem?
I wouldn't like to bring all information to excel and after transform
it to unique values only... Is there other way to do it?

Thanks a lot!!!

Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As
String, _
ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As
Boolean) As ADODB.Recordset

Dim strConnection As String
Dim filenm As String

On Error GoTo ErrorHandler

filenm = ThisWorkbook.Path & "\controle_despesas.mdb"

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
filenm & ";"

Set RunQuery = New ADODB.Recordset
With RunQuery
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With


RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy, strConnection, , , adCmdText

If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing
Exit Function

ErrorHandler:
MsgBox Err.Description
End Function

Sub expenses_period()

Dim rst As ADODB.Recordset
Dim strValidationList As String
Dim strValidationList2 As String
Dim strValidationList3 As String


Set rst = RunQuery("Select *", "From period", "", ";", False)
rst.MoveFirst
strValidationList = rst.Fields("year_month").UnderlyingValue
strValidationList2 = rst.Fields("year_quater").UnderlyingValue
strValidationList3 = rst.Fields("year").UnderlyingValue
rst.MoveNext
Do While Not rst.EOF
strValidationList = strValidationList & ", " &
rst.Fields("year_month ").UnderlyingValue
strValidationList2 = strValidationList2 & "," &
rst.Fields("year_quater ").UnderlyingValue
strValidationList3 = strValidationList3 & "," & rst.Fields("year
").UnderlyingValue
rst.MoveNext
Loop

MsgBox strValidationList, vbInformation
MsgBox strValidationList2, vbInformation
MsgBox strValidationList3, vbInformation

'Range("D6:IV6").Validation.Delete
'Range("D6:IV6").Validation.Add xlValidateList, , , strValidationList

End Sub

Thanks a lot!!!

André.

 

[Guest]

Do you include the DISTINCT statement in your SELECT clause? It will return
unique values.

Example:
SELECT DISTINCT field1 FROM database;


Does that help?
***********
Regards,
Ron

XL2003, WinXP

 

[gatarossi]

Dear Ron,

Thanks a lot, It looks easy but nobody knowns, I have tried to obtain
this answer for a long time...

Thanks a lot!!!

André.