How to use a Variable in sql search

[SIDNEY]

I am using VB6 and having a lot of truble<br>
getting the syntex for the following peace of code<br>
<br>
aib = "D H L"<br>
Dim dbs As Database, rst As Recordset<br>
Set dbs = OpenDatabase("V:\throp\thropPCS.mdb")<br>
Set rst = dbs.OpenRecordset("SELECT customerlist FROM customerlist, where customerlist = [aib] ;")<br>
<br>
the code works fine when used with a const <br>
ie, Where customerlist = 'D H L' ;") but not with a veriable

 

[bitbrain]

try:<br>
<br>
Set rst = dbs.OpenRecordset("SELECT customerlist FROM customerlist, where customerlist = """ & [aib] & """)<br>

 

[calahans]

Sidney,<br>
<br>
I think bitbrain's solution may give you an error, you should try:<br>
<br>
Set rst = dbs.OpenRecordset("SELECT customerlist FROM customerlist where customerlist = """ & aib & """)<br>
<br>
ie remove the square brackets from your variable name. Also remove the comma after your table name.<br>
<br>
C

 

[bitbrain]

The square brackets are optional in this case. They are required for column names that have embedded spaces (i.e. [Customer Name]). Some people may feel that it is good coding practice to use them consistently.<br>
<br>
I agree about the comma after the FROM - it should be removed.<br>
<br>
The point to the response was that the SQL string should be broken into pieces so that the variable portion can be concatenated to the literal portions.<br>
<br>
<br>
<br>

 

[SIDNEY]

thanks a lot lads<br>
I have it working fine now<br>