Variable in where statement of database query SQL

[zyeagle17]

I am trying to make a tool based using databases.

What i need to do is create a connection query based on a user input.

I can get it all to work until i try to add variables into the where statement at the end of the SQL string.

Here is the beginning of the code that I have



Dim range1 As String
Dim range2 As String
Dim range3 As String
Dim cta As String

Dim counts As Long

mypath = Sheets("load").Range("t6").Value
range1 = Sheets("load").Range("T1").Value
range2 = Sheets("load").Range("T2").Value
range3 = Sheets("load").Range("T3").Value
cta = Sheets("load").Range("t4").Value
packtype = [lo_packtype]
Sheets("load").Range("w1").Value = Sheets("load").Range("w1") + 1
counts = Sheets("load").Range("w1").Value
packtype1 = Sheets("load").Range("f7").Value

Dim wheres As String
wheres = "WHERE (" & range1 & ".pack_tier_dummy='packtype') AND (" & range1 & ".pack_tier_value=" & Chr(39) & "#" & packtype1 & "#" & Chr(39) & ")"

Workbooks("access_enabled_tool_5242012.xlsm").Connections.Add "Query from MS Access Database" & counts _
, "", Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Users\******\Desktop\HER_NewTool\ACMTTLCTA.accdb;DefaultDir=C:\Users\******\Desktop\H" _
), Array( _
"ER_NewTool;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), _
Array( _
"SELECT " & range1 & ".pack_tier_dummy, " & range1 & ".pack_tier_value, " & range1 & ".hol_period_flag, " & range1 & ".condition_base, " & range1 & ".t" _
, _
"pr_percent, " & range1 & ".lift, " & range1 & ".predicted_base, " & range1 & ".predicted_feature, " & range1 & ".predicted_display, " & range1 & ".pre" _
, _
"dicted_feat_disp, " & range1 & ".predicted_pct_dsct_efctv, " & range1 & ".pct_dsct_average, " & range1 & ".predicted_base_ttl, " & range1 & ".predicted_base_ttl" _
, _
"_2010, " & range1 & ".predicted_incremental_2010, " & range1 & ".predicted_base_ttl_2011, " & range1 & ".predicted_incremental_2011, " & range1 & ".base_growth_" _
, _
"rate, " & range1 & ".incremental_growth_rate" & Chr(13) & "" & Chr(10) & "FROM " & mypath & "\" & cta & ".accdb`." & range1 & " " & range1 & Chr(13) & "" & Chr(10) & wheres), 2

I tried submitting the where statement as its own string, but i still get a type mismatch error

Thanks