asked on

Getting criteria list for "WHERE" of TSQL from Excel

Hello To All,

Need help in structuring a Select query….

I am writing a VBA code that will run from a excel file, grab a set of dates from sheet 3and then pull data of a table in SQL Server using that set of dates as a criteria set….How to do it?

SELECT [OrderID]
,[DateOfIssue]
,[UnitPrice]
,[Quantity]
,[Discount]
FROM [Northwind].[dbo].[Order Details]
where DateOfIssue in (select Dates from sheet3.Range("A:A50000"))

In excel file, in column A of sheet3, has a list of dates that can vary from few dates to 200-300

Sending this SQL Query from Excel VBA, - when this select statment is executed, the "Where" criteria looks at a set of dates in column B of sheet3 for getting the query results
Not sure if my query is set up correctly.

Rayne

ASKER

Getting somewhat but still not getting in the groove

Dim strSQLStatement As String
Dim strVALSet As String
Dim clVal As String
Dim col, Count, lastRw, rw As Long
Dim dateWks As Worksheet

Set dateWks = Sheet2

col = 1

lastRw = dateWks.Range("A" & Rows.Count).End(xlUp).Row

For rw = 2 To lastRw
strVALSet = "[IssueDate] ="
'strVALSet = ""
clVal = dateWks.Range("A" & rw)
strVALSet = strVALSet & ", '" & clVal & "'"
'strSQLStatement = strSQLStatement & "," & vbCrLf & " or(" & Mid(strVALSet, 3) & ")"
strSQLStatement = strSQLStatement & "," & vbCrLf & " or(" & Mid(strVALSet, 3) & ")"
Next
Stop
Debug.Print "WHERE [IssueDate] =" & Mid(strSQLStatement, 3)

Rayne

ASKER

please assist

thanks

ASKER CERTIFIED SOLUTION