Solved

Getting criteria list for  "WHERE"  of TSQL  from Excel

Posted on 2013-01-04
5
162 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 500 total points
ID: 38746116
0
 
LVL 43

Assisted Solution

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

Author Closing Comment

by:Rayne
ID: 38746154
thanks Eugene :)
0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

717 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