Solved

Getting criteria list for  "WHERE"  of TSQL  from Excel

Posted on 2013-01-04
5
158 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
  • 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 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 38746116
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 500 total points
ID: 38746123
0
 

Author Closing Comment

by:Rayne
ID: 38746154
thanks Eugene :)
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now