?
Solved

Getting criteria list for  "WHERE"  of TSQL  from Excel

Posted on 2013-01-04
5
Medium Priority
?
168 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 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
I came across an unsolved Outlook issue and here is my solution.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

807 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