?
Solved

Getting criteria list for  "WHERE"  of TSQL  from Excel

Posted on 2013-01-04
5
Medium Priority
?
163 Views
Last Modified: 2013-01-04
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.
0
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 

Author Comment

by:Rayne
ID: 38745793
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)
0
 

Author Comment

by:Rayne
ID: 38745795
please assist

thanks
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 38746116
0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 2000 total points
ID: 38746123
0
 

Author Closing Comment

by:Rayne
ID: 38746154
thanks Eugene :)
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question