SQL instruction

[gatarossi]

Dear all,

I don't known nothing about SQL - it's my big problem in excel!

Now I'm trying to import some datas from ms acess whith some
criterias, but I don't know how to create a SQL instruction.

I did a form in excel with two textbox: txtgm and txtdate - the user
will put the information, and the excel will cath only this
information from access database.

My database calls cost.
My table calls gross_margin
The specific fields in my database is: pi and date

This is the code, but I don't know how to change the SQL instruction:

Sub gross_margin()
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String

sql = "select * from gross_margin"
'sql = sql & "WHERE pi = txtgm.value AND date = txtdate"?

filenm = "J:\Andre\Custos\custo_pedidos.mdb"
Call GetCn(adoconn, adors, sql, filenm, "", "")

Dim xlsht As Excel.Worksheet
Set xlsht = Sheets("Plan1")
xlsht.Range("a1").CopyFromRecordset adors

adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlsht = Nothing
End Sub


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

Thanks,

André.

 

[Bob Phillips]

Try

sql = sql & "WHERE pi = " & txtgm.value & "AND date = " & txtdate.Text

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Dear all,

I don't known nothing about SQL - it's my big problem in excel!

Now I'm trying to import some datas from ms acess whith some
criterias, but I don't know how to create a SQL instruction.

I did a form in excel with two textbox: txtgm and txtdate - the user
will put the information, and the excel will cath only this
information from access database.

My database calls cost.
My table calls gross_margin
The specific fields in my database is: pi and date

This is the code, but I don't know how to change the SQL instruction:

Sub gross_margin()
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String

sql = "select * from gross_margin"
'sql = sql & "WHERE pi = txtgm.value AND date = txtdate"?

filenm = "J:\Andre\Custos\custo_pedidos.mdb"
Call GetCn(adoconn, adors, sql, filenm, "", "")

Dim xlsht As Excel.Worksheet
Set xlsht = Sheets("Plan1")
xlsht.Range("a1").CopyFromRecordset adors

adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlsht = Nothing
End Sub


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

Thanks,

André.