pass variable from Inputbox to sql query

[dogsbod]

hi all,

I've pulled my hair out on this one, it took days to work out to use the ` instead of ' for selecting columns with spaces in their name (not my idea). I'm now stumped again and would love any help.

y serves as the variable passed to sql select statement by the input box

y = accounts

Const adOpenStatic = 3
Const adLockOptimistic = 3


Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")


objConnection.Open _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=c:\AT.mdb"

objRecordset.Open "SELECT * FROM `main table` where Dept = 'y' " ,_
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
Wscript.Echo objRecordSet.Fields.Item("dept")
objRecordSet.MoveNext
Loop

The bit = 'y' " is where I'm stuck. If you use the actual value of y ie accounts, then it must be enclosed in ' eg 'accounts'. However I can't do 'y'. I've tried [],(), "
etc to try and get this to work

 

[PHV]

You may try this:

objRecordset.Open "SELECT * FROM `main table` where Dept = ' & y & "' " ,_

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dogsbod]

I worked it out needed to do

"SELECT * FROM table" & " " & "where value ='" & y & "'

 

[dogsbod]

sorry, never refreshed page before replying. Thanks for the response

 

[simian101]

Sometimes it is easier to use chr(34) for "
and chr(39) for '

ex,

"'" & chr(34) & "'" is equal to '"'

Simi