Variable in where statement of database query SQL

zyeagle17

New Member
Joined
May 25, 2012
Messages
2
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
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Forum statistics

Threads
1,136,434
Messages
5,675,834
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top