Pull SQL Data into Excel by Date Range

[twmills]

Hello,

I'm looking to grab data from an SQL table, but only pull data based on the date range specified in excel. For example, on the BI Reports sheet (cell J2) there's a start date of 2022-08-01. Then an end date of 2022-08-31 in cell J3. I was hoping to have it pull data that only falls between that date range, including the start and end date. I'm using that format (yyyy-mm-dd), because that's how it appears on the SQL table.

I'm guessing the bold section below has to be adjusted in some way.



Dim conn As ADODB.Connection
Dim stFund, stYear As String
Dim rs As ADODB.Recordset
Dim stSQL As String
Set conn = New ADODB.Connection
conn.Open xConn
Set rs = New ADODB.Recordset
stSQL = "SELECT [ADJ_Table].[ID]"
stSQL = stSQL + " , [AdjProcessError].[ProcessErrorID]"
stSQL = stSQL + " , [AdjustmentDate]"
stSQL = stSQL + " , [WorkType]"
stSQL = stSQL + " , [ReasonCode]"
stSQL = stSQL + " , [FundNo]"
stSQL = stSQL + " , [ShareholderAccountNumber]"
stSQL = stSQL + " , [DescriptionofProblem]"
stSQL = stSQL + " FROM [AdjProcessError] INNER JOIN [ADJ_Table] ON [AdjProcessError].RequestNbr = [ADJ_Table].ID"
stSQL = stSQL & " WHERE (([AdjProcessError].[ProcessErrorDepartment]) = '3D') OR (([AdjProcessError].[ProcessErrorDepartment]) = '3D Team 1') OR (([AdjProcessError].[ProcessErrorDepartment]) = '3D Team 2') OR (([AdjProcessError].[ProcessErrorDepartment]) = '3D Team 3') OR (([AdjProcessError].[ProcessErrorDepartment]) = '3D Manager') OR (([AdjProcessError].[ProcessErrorDepartment]) = 'Records Research')"
'stSQL = stSQL & " WHERE (Year([AdjustmentDate]) =" & Year(Now()) & " And (([AdjProcessError].[ProcessErrorDepartment]) = '3D')) OR (Year([AdjustmentDate]) =" & Year(Now()) & " And (([AdjProcessError].[ProcessErrorDepartment]) = '3D Team 1')) OR (Year([AdjustmentDate]) =" & Year(Now()) & " And (([AdjProcessError].[ProcessErrorDepartment]) = '3D Team 2')) OR (Year([AdjustmentDate]) =" & Year(Now()) & " And (([AdjProcessError].[ProcessErrorDepartment]) = '3D Team 3')) OR (Year([AdjustmentDate]) =" & Year(Now()) & " And (([AdjProcessError].[ProcessErrorDepartment]) = '3D Manager')) OR (Year([AdjustmentDate]) =" & Year(Now()) & " And (([AdjProcessError].[ProcessErrorDepartment]) = 'Records Research'))"
stSQL_Date = " AND [AdjustmentDate] Between " & "#" & Sheets("BI Reports").Cells(2, 10) & "# And #" & Sheets("BI Reports").Cells(3, 10) & "#"
Set rs = conn.Execute(stSQL)
Sheets("Adjustments").Select
Range("A2:H100000").Select
Selection.ClearContents
Range("A2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

[JKwan]

I think this is what you need:

stSQL = "SELECT [ADJ_Table].[ID]" stSQL = stSQL + " , [AdjProcessError].[ProcessErrorID]"
stSQL = stSQL + " , [AdjustmentDate]"
stSQL = stSQL + " , [WorkType]"
stSQL = stSQL + " , [ReasonCode]"
stSQL = stSQL + " , [FundNo]"
stSQL = stSQL + " , [ShareholderAccountNumber]"
stSQL = stSQL + " , [DescriptionofProblem]"
stSQL = stSQL + " FROM [AdjProcessError] INNER JOIN [ADJ_Table] ON [AdjProcessError].RequestNbr = [ADJ_Table].ID"
stSQL = stSQL & " WHERE (([AdjProcessError].[ProcessErrorDepartment]) IN ('3D', '3D Team 1', '3D Team 2', '3D Team 3', '3D Manager', 'Records Research')"
stSQL = stSQL & " AND [AdjustmentDate] Between " & "(#" & Sheets("BI Reports").Cells(2, 10) & "# And #" & Sheets("BI Reports").Cells(3, 10) & "#)"




did you get an error when you did your SQL?
or maybe your date in cell not in the correct format (yyyy-mm-dd ?)

if it is formating the date, use line below

stSQL = stSQL & " AND [AdjustmentDate] Between " & "(#" & Format(Sheets("BI Reports").Cells(2, 10), "yyyy-mm-dd") & "# And #" & Format(Sheets("BI Reports").Cells(3, 10), "yyyy-mm-dd") & "#)"

[twmills]

I think this is what you need:

stSQL = "SELECT [ADJ_Table].[ID]" stSQL = stSQL + " , [AdjProcessError].[ProcessErrorID]"
stSQL = stSQL + " , [AdjustmentDate]"
stSQL = stSQL + " , [WorkType]"
stSQL = stSQL + " , [ReasonCode]"
stSQL = stSQL + " , [FundNo]"
stSQL = stSQL + " , [ShareholderAccountNumber]"
stSQL = stSQL + " , [DescriptionofProblem]"
stSQL = stSQL + " FROM [AdjProcessError] INNER JOIN [ADJ_Table] ON [AdjProcessError].RequestNbr = [ADJ_Table].ID"
stSQL = stSQL & " WHERE (([AdjProcessError].[ProcessErrorDepartment]) IN ('3D', '3D Team 1', '3D Team 2', '3D Team 3', '3D Manager', 'Records Research')"
stSQL = stSQL & " AND [AdjustmentDate] Between " & "(#" & Sheets("BI Reports").Cells(2, 10) & "# And #" & Sheets("BI Reports").Cells(3, 10) & "#)"




did you get an error when you did your SQL?
or maybe your date in cell not in the correct format (yyyy-mm-dd ?)

if it is formating the date, use line below

stSQL = stSQL & " AND [AdjustmentDate] Between " & "(#" & Format(Sheets("BI Reports").Cells(2, 10), "yyyy-mm-dd") & "# And #" & Format(Sheets("BI Reports").Cells(3, 10), "yyyy-mm-dd") & "#)"



Hi JK,

No, I wasn't getting an error...it ran all the way through but returned all data, rather than just the range I need.

I tried both of the date range codes above and I'm getting the same error message.

Run-time error '-2147217900 (80040e14)':
Incorrect syntax near '#'.

Ideally, what format should the cells of (2,10) and (3, 10) be? I would assume the same as how it appears in the SQL table, right?

Thanks for your help.

[JKwan]

Sorry, I don't have SQL Server, the best that I can do is get the SQL statement for you....