Bring Data from Access

[gatarossi]

Dear all,

I'm trying to do bring some information from access to excel, but
sometimes I have a criteria and sometimes I don't need a criteria.

For example: I can choose only the month January in my criteria, or if
I put nothing it will bring all year of 2007.

There are a lot of criteria in my sheet, but if I discover how to do
in this case, I will do the same for the others criteria.

This is the code that I'm working:

Sub return_values_qty_2()

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("qty")

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

col = ActiveCell.Column

sql = "SELECT Sum(sales_quantity) AS Expr1 FROM Table1 "
sql = sql & "WHERE (((division) Like IIf('" & Cells(4, col) & "' Is
Null,'*','" & Cells(4, col) & "'))) ;"
Call getCn(adoconn, adors, sql, filenm, "", "")
xlsht.Cells(9, col).CopyFromRecordset adors

adors.Close
adoconn.Close

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

End Sub

In this case, if there isn't data in this cell, the code doesn't work,
but if there is data it´s work.

I need if in the cell there isn't information, the excel bring all
information, without criteria.

Thanks in advance!!!!

André.

 

[JW]

Untested, but give something like this a shot.
Set the criteria before you call your sql instead of using an IIF
statement.
Dim criteria As String
If IsEmpty(Cells(4, Col)) Then
criteria = "*"
Else
criteria = Cells(4, Col)
End If
Sql = "SELECT Sum(sales_quantity) AS Expr1 " & _
"FROM Table1 WHERE (((division) Like " & _
criteria & ";"

 

[gatarossi]

Dear JW,

This code works in access, because I made a code to excel create a
consult in ms access, and running this consult in access it works,
then the code is correct.

But when I run this new code in excel, it doesn't bring the value, it
only work if I put a criteria.

I really don´t known why it is happening...

Thanks a lot!

André.

 

[Ron de Bruin]

hi André

Maybe this will help
http://www.rondebruin.nl/accessexcel.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


Dear all,

I'm trying to do bring some information from access to excel, but
sometimes I have a criteria and sometimes I don't need a criteria.

For example: I can choose only the month January in my criteria, or if
I put nothing it will bring all year of 2007.

There are a lot of criteria in my sheet, but if I discover how to do
in this case, I will do the same for the others criteria.

This is the code that I'm working:

Sub return_values_qty_2()

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("qty")

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

col = ActiveCell.Column

sql = "SELECT Sum(sales_quantity) AS Expr1 FROM Table1 "
sql = sql & "WHERE (((division) Like IIf('" & Cells(4, col) & "' Is
Null,'*','" & Cells(4, col) & "'))) ;"
Call getCn(adoconn, adors, sql, filenm, "", "")
xlsht.Cells(9, col).CopyFromRecordset adors

adors.Close
adoconn.Close

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

End Sub

In this case, if there isn't data in this cell, the code doesn't work,
but if there is data it´s work.

I need if in the cell there isn't information, the excel bring all
information, without criteria.

Thanks in advance!!!!

André.