Exclude Data of Access

[gatarossi]

Dear all,
I'm trying to exclude some data in my access table, but the code
bellow doesn't work. I need that excel to exclude all data in the
table that contains a criteria in two range of excel.
It's the message error: "no value was supplied for an or more
necessary parameters"

For example:
Table
accounting_code profit_center date value
salary 3 01/01/07 100


I have the range a1 for the profit_center and range a2 for the date.
Then I will put in range a1 = 3 and a2 = 01/01/07, then I will click
in a button and excel will exclude all data in the table that
contains
profit_center = 3 and date = 01/01/07.


Sub exclui_teste()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Myconn = "C:\Documents and Settings\brspagi\Meus documentos\teste
\controle_despesas.mdb"
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open Myconn
.Execute "Delete * from orcamento_despesas Where profit_center =
" & range("A1") & " and date = '#" & range("A2") & "'#"
.Close
End With
End Sub


Thanks,


André.

 

[Rodrigo Ferreira]

O campo na sua tabela tem hora?
Se tiver, tente incluir
date >= '#" & range("A2") & "'#" and date < '#" & dateadd("d", range("A2"))
& "'#"

--

Rodrigo Ferreira
Regards from Brazil


<[email protected]> escreveu na mensagem
Dear all,
I'm trying to exclude some data in my access table, but the code
bellow doesn't work. I need that excel to exclude all data in the
table that contains a criteria in two range of excel.
It's the message error: "no value was supplied for an or more
necessary parameters"

For example:
Table
accounting_code profit_center date value
salary 3 01/01/07 100


I have the range a1 for the profit_center and range a2 for the date.
Then I will put in range a1 = 3 and a2 = 01/01/07, then I will click
in a button and excel will exclude all data in the table that
contains
profit_center = 3 and date = 01/01/07.


Sub exclui_teste()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Myconn = "C:\Documents and Settings\brspagi\Meus documentos\teste
\controle_despesas.mdb"
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open Myconn
.Execute "Delete * from orcamento_despesas Where profit_center =
" & range("A1") & " and date = '#" & range("A2") & "'#"
.Close
End With
End Sub


Thanks,


André.

 

[gatarossi]

Rodrigo,

There isn't hour in the field in my table. I really don't know why it
is happening.

Thanks,

André.

 

[Rodrigo Ferreira]

Try to put your sql string on access to test
Or create a new "consulta" to delete the data then compare the sql string
from access.
Or try to format the date (something like format(Range(A3), "dd/mm/yyyy"))

--

Rodrigo Ferreira
Regards from Brazil


<[email protected]> escreveu na mensagem
Rodrigo,

There isn't hour in the field in my table. I really don't know why it
is happening.

Thanks,

André.