MS Excel VBA DAO SQL where clause cell

[bbcdancer]

Hi

I'm trying to get a table of data from MS Access to MS Excel via VBA
DAO programming.

In my worksheet call 'Data' in cell D14 there is my string value call
"toys & Chairs"

I can get the DATA into MS Excel without the Where clause in my SQL
VBA script. The problem is when I use the WHERE clause which sources
its value from cell D14...

In my VBA code.

Firstly, cell D14 is defined in VBA:


Dim r1 As String

r1 = Range("D14")



Secondly, MY SQL is:

SQL = "LVL_1, LVL_2, LVL_3"
SQL = SQL & " FROM table_ABC"
SQL = SQL & " WHERE LVL_2 = " & r1 & ";"


When I run the VBA code, I get a error message:

Run-time '91':
Object variable or With block variable not set



Can any help me with this problem?

Many thanks in advance.

Brenda

 

[J_Goddard via AccessMonster.com]

Hi -

Please post your code so we can see it, and tell us where the error occurs -
otherwise we can only guess.

Your SQL as posted is incorrect - it is missing the "SELECT" (if that is what
is supposed to do), and there should be (single) quotes around r1, since it
is text.

John

Hi

I'm trying to get a table of data from MS Access to MS Excel via VBA
DAO programming.

In my worksheet call 'Data' in cell D14 there is my string value call
"toys & Chairs"

I can get the DATA into MS Excel without the Where clause in my SQL
VBA script. The problem is when I use the WHERE clause which sources
its value from cell D14...

In my VBA code.

Firstly, cell D14 is defined in VBA:

Dim r1 As String

r1 = Range("D14")

Secondly, MY SQL is:

SQL = "LVL_1, LVL_2, LVL_3"
SQL = SQL & " FROM table_ABC"
SQL = SQL & " WHERE LVL_2 = " & r1 & ";"

When I run the VBA code, I get a error message:

Run-time '91':
Object variable or With block variable not set

Can any help me with this problem?

Many thanks in advance.

Brenda

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com