Database query variable

I have an Excel 2000 file set up to query an Acess 2000 database. The code works fine, but now I need the ability to query the database table based on a variable. Below is my current code and where I set the variable and think the variable should be in the query string(highlighted in red):

Please Login or Register  to view this content.

strTerr is where my variable is stored and is a 4 digit code that is formatted as Text because strTerr could begin with a 0, ie 0214. When I run the code as given above, I get "SQL Syntax Error." I think I'm on the right track, but cannot figure out the correct syntax, any ideas?
Your help is greatly appreciated. Thanks

have you tried using ADO, something a bit like the below?
open the conn then open the rst passing in the sql.

you can then build up the sql outside of you connection string, probably be faster than the method you are currently employing (and easy to debug!).

cheers,
Matt.


Public conn As ADODB.Connection
Public rst As ADODB.Recordset
Public MinVal As Double

Function OpenConn()
DBasePath = <UNCPath>

Set conn = New ADODB.Connection
With conn
.Provider = "Microsoft.JET.OLEDB.4.0"
.Open DBasePath
End With
End Function

Function CloseConn()
conn.Close
Set conn = Nothing
End Function

Function OpenRst(ByVal sqlstr As String, Optional ByRef clientcol, Optional CountType As String, Optional MinVal As Double)
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = conn
.Open sqlstr, conn
End With

variableCount = rst.Fields.Count
variableValue = rst![column name]
End Function

Function CloseRst()
Set rst = Nothing
End Function