Unique Values

G

gatarossi

Dear all,

I'm importing some data from access. In my table, I can have duplicate
data, for example:

accounting_code profit_center period value
10 3 0701 100
10 3 0702 101

In this code below, I'm trying to return in excel all accounting_code
form a determined profit_center, but I need only unique values... How
can i do it?

Public Sub getCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As
ADODB.Recordset, _
sqlstr As String, dbfile As String, usernm As String, pword As String)

Set dbcon = New ADODB.Connection
dbcon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfile &
";", _
usernm, pword

Set dbrs = New ADODB.Recordset
dbrs.Open sqlstr, dbcon

End Sub

Sub retorna_valores()

Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String
Dim xlsht As Excel.Worksheet

Set xlsht = Sheets("despesas")

sql = "SELECT codigo_conta_contabil from orcamento_despesas "
sql = sql & "Where codigo_centro_custo= '" & Cells(3, 2) & "' ;"

filenm = "C:\Documents and Settings\andre\Meus documentos\despesas
\controle_despesas.mdb"

Call getCn(adoconn, adors, sql, filenm, "", "")

xlsht.Cells(7, 1).CopyFromRecordset adors

adors.Close
adoconn.Close

Set adors = Nothing
Set adoconn = Nothing
Set xlsht = Nothing

End Sub

Thanks a lot!!!

André.
 
H

Harlan Grove

CLR said:
I use Jim Cone's fine commercial program called XL Companion for this.
....

It may have its merits, but the OP's task is pretty simple.
"(e-mail address removed)" wrote: ....
....

Make the SQL statement

sql = "SELECT codigo_conta_contabil" & vbLF &_
"FROM orcamento_despesas" & vbLF & _
"GROUP BY codigo_conta_contabil, codigo_centro_custo" & vbLF & _
"HAVING codigo_centro_custo = '" & Cells(3, 2) & "' ;"

to fetch only the distinct codigo_conta_contabil entries.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Bring Data From Access in a Form 0
'*' in sql instruction 0
SQL instruction 1
Bring the heading data in a sql query 0
ADO - password 3
SQL - Sum Values 0
Database in Excel 0
Bring Data from Access 3

Top