• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

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?

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.
  • 3
  • 2
2 Solutions
RayneAuthor Commented:
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) & ")"
    Debug.Print "WHERE [IssueDate] =" & Mid(strSQLStatement, 3)
RayneAuthor Commented:
please assist

Eugene ZCommented:
Eugene ZCommented:
RayneAuthor Commented:
thanks Eugene :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now