Read from SQL variable column name to excel sheet

[robert_645]

Hello all,

I hope somebody can help me out.

In my code the SQL column name is a variable.
The columns are named: KWR1, KWR1, KWR3 and KWR4 (where I use a variable for the number (mytel2))
The choice is made in an userform (4 choices).
Afterwards I would like to copy the data from one of the columns in a range.
Below is the code I have, so far, but get an error at: Range("A" & mytel2) = rc![" & kolom & "]

...
kolom = "KWR" & mytel

rc.Open "SELECT [" & kolom & "] FROM " & jaar & " WHERE [" & kolom & "] != ''", con
rc.MoveFirst

Do
Range("A" & mytel2) = rc![" & kolom & "]
mytel2 = mytel2 + 1
rc.MoveNext
Loop Until rc.EOF

rc.Close
...

[5631]

What is in rc![" & kolom & "]? Does the recordset open at all?

[robert_645]

I am sorry, I don't understand exactly what you mean?
rc![" & kolom & "] = the data from column KWR1 or KWR2 or KWR3 or KWR4.
I have opened the recordset in a part of the code just above the code which is displayed in post 1.

The strange thing is, that the variable in rc.Open "SELECT [" & kolom & "] FROM " & jaar & " WHERE [" & kolom & "] != ''", con
works fine, but when I would like to retrieve the data to copy that in the cell in Excel, I get an error
Range("A" & mytel2) = rc![" & kolom & "]

[5631]

Debug.Print rc![" & kolom & "]

[Norie]

How many records are returned?

What error do you get with this code?

Range("A" & mytel2) = rc![" & kolom & "]

Does this work?

Range("A" & mytel2) = rc.Fields(kolom)

[robert_645]

Sorry for my late response, but....

Thanks!!! using rc.Fields(kolom) works great!
Problem solved