VBA code to pull in data from SQL table

[twmills]

Hello,

Using VBA code in excel, I'm looking to extract data from a SQL table. But I just need it to pull current year information from it. The code works fine without the WHERE statement, but that brings is the entire table when I'm just looking for current year stuff. When I add in the WHERE statement, I'm getting the following error message:

Run-time error '-2147217900 (80040e14)':
'now' is not a recognized built-in function name.



Sub UpdateData()
Dim conn As ADODB.Connection
Sheets("Input").Visible = True
Sheets("Input").Select
Range("A2:AE100000").ClearContents
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 * from dData"
stSQL = stSQL & " WHERE Year([dDate]) = year(now())"
Set rs = conn.Execute(stSQL)
Range("A2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
Sheets("Input").Visible = False
End Sub

[JKwan]

I am guessing that you want current year, so below is what I think you need

stSQL = stSQL & " WHERE Year([dDate]) =" & year(now())

[georgiboy]

Not that great with SQL however from what i have looked at should:

stSQL = "Select * from dData"
Be

stSQL = "Select Date from dData"

[twmills]

I am guessing that you want current year, so below is what I think you need

stSQL = stSQL & " WHERE Year([dDate]) =" & year(now())


Yes, that seemed to do the trick.

Thanks so much!!

[twmills]

Sorry, looks like I need to take this one step further. I'm looking to add an additional WHERE statement. So the data that gets returned should be Current Year AND have the department name = 3D. I know I'm close, but I can't seem to figure out the apostrophe or quote mark alignments. I bolded the line below that's giving me issues.



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 (Year ([AdjustmentDate]) =" & Year(Now()) And ([AdjProcessError].[ProcessErrorDepartment]) =" & "'3D'"
stSQL = stSQL + " ORDER BY [ADJ_Table].[ID] DESC"
Set rs = conn.Execute(stSQL)
Sheets("External Errors Detail").Select
Range("A2:H700000").ClearContents
Range("A2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

[twmills]

I am guessing that you want current year, so below is what I think you need

stSQL = stSQL & " WHERE Year([dDate]) =" & year(now())

Sorry, looks like I need to take this one step further. I'm looking to add an additional WHERE statement. So the data that gets returned should be Current Year AND have the department name = 3D. I know I'm close, but I can't seem to figure out the apostrophe or quote mark alignments. I bolded the line below that's giving me issues.



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 (Year ([AdjustmentDate]) =" & Year(Now()) And ([AdjProcessError].[ProcessErrorDepartment]) =" & "'3D'"
stSQL = stSQL + " ORDER BY [ADJ_Table].[ID] DESC"
Set rs = conn.Execute(stSQL)
Sheets("External Errors Detail").Select
Range("A2:H700000").ClearContents
Range("A2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

[arnelgp]

..
..
stSQL = stSQL & " WHERE (Year([AdjustmentDate]) =" & Year(Now()) & " And (([AdjProcessError].[ProcessErrorDepartment]) = '3D')"

[twmills]

..
..
stSQL = stSQL & " WHERE (Year([AdjustmentDate]) =" & Year(Now()) & " And (([AdjProcessError].[ProcessErrorDepartment]) = '3D')"

Looks like it needed an extra ) at the end, but I got it to work.

Thanks!!