Get value from sql where its select statement is a variable

[vinceleww]

Hi all,

I using VB and Ms Access as database environment. My sql statement has no problem. But i have error line at List2.AddItem CStr(rs3("" & pTot & "")), which i want to print out the data to listbox.i know this is a syntax error, but i have no idea how to fix it.


Following is my code:

openConn
sql = "select field_name from " & Click & ""
rs.Open sql, cn
If Not rs.EOF Then
rs.MoveFirst

Do While Not rs.EOF
pTot = rs("field_name")

sql = "select '" & pTot & "' from DWH_ADMIN_DWH_WIP_DATA where operation=" Advantest" "
rs3.Open sql, cn
If Not rs3.EOF Then
rs3.MoveFirst

Do While Not rs3.EOF
List2.AddItem CStr(rs3("" & pTot & ""))
rs3.MoveNext
Loop

End If
rs3.Close
rs.MoveNext
Loop
End If
rs.Close
cn.Close

Anybody can help me.....Hope to hear from u all soon....thanks thanks a lot...