twmills
09-01-2022, 12:16 PM
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
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