VBA: SQL Server to Excel (Query)

jnmad0127

New Member
Joined
Mar 24, 2015
Messages
5
Hi All,

I'm in need of assistance. I found this VBA code in some other forum and tried editing it and Im a bit not a master of vba. What I kinda need to do is to integrate my sql query, but im not having any luck, im getting an error when im placing my where and group clause in the VBA code and the SUm part:confused:

VBA code
Code:
 Sub ConnectSqlServer()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    Dim queryDef As String
   ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=C3auat001;" & _
                  "Initial Catalog=C3_Analytics;" & _
                  "Integrated Security=SSPI;"
    
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    ' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute("SELECT [row_date],[split],[loc_id], sum([acdcalls]) FROM [C3_Analytics].[CMS].[hagent];Where Month([row_date])=3 AND Year([row_date])=2015;Group by [row_date],[split],[loc_id];")
    
    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets(1).Range("A1").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If
    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
    
End Sub


SQL Code
Code:
SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000][row_date][/COLOR]
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]/* ,[logid]*/
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][split]
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][loc_id]
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]/* Average handling time */
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][acdcalls][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] calls_handled
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][i_acdtime][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] i_acdtime
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][i_acwtime][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] i_acwtime
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][holdacdtime][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] holdacdtime
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][acdtime][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] acdtime
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][acwtime][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] acwtime
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][holdtime][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] holdtime
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][abncalls][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] abncalls
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][abntime][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] abntime
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][transferred][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Transferred
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][conference][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] conference
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]/* occupancy/productivity */[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ti_stafftime][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ti_stafftime
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ti_availtime][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ti_availtime
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ti_auxtime][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ti_auxtime
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ti_auxtime0][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ti_auxtime0
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ti_auxtime1][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ti_auxtime1
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ti_auxtime2][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ti_auxtime2
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ti_auxtime3][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ti_auxtime3
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ti_auxtime4][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ti_auxtime4
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ti_auxtime5][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ti_auxtime5
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ti_auxtime6][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ti_auxtime6
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ti_auxtime7][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ti_auxtime7
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ti_auxtime8][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ti_auxtime8
[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ti_auxtime9][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ti_auxtime9
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]/* inner join table from DBO.skillLob table */[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]/*
,[tblSkillLob].[Skill]
,[tblSkillLob].[Client]
,[tblSkillLob].[Lob]
,[tblSkillLob].[ProjectNumber]
,[tblSkillLob].[DeptID]
*/
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [C3_Analytics][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][CMS][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][dagent]
[/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]/* 
INNER JOIN [C3_Analytics].[dbo].[tblSkillLob]
On [tblSkillLob].[Skill]=[dagent].[split]
*/[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Where[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]Month[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][row_date][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]3 [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]Year[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][row_date][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]2015
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Group[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]by[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [row_date][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][split][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][loc_id][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080];

[/COLOR][/SIZE][/COLOR][/SIZE]
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
There's no space after 'Where' or 'AND'.
 

jnmad0127

New Member
Joined
Mar 24, 2015
Messages
5
Thank you for the response. I did get to make the Where statement work but when adding the sum([acdcalls]) and the Group by statement im still always getting an error.

Code:
Set rs = conn.Execute("SELECT [row_date],[split],[loc_id], sum([acdcalls]) FROM [C3_Analytics].[CMS].[hagent] Where Month([row_date])=3 AND Year([row_date])=2015 Group by [row_date],[split],[loc_id];")
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,744
What's the error? There's nothing wrong with that sql
 

jnmad0127

New Member
Joined
Mar 24, 2015
Messages
5

ADVERTISEMENT

i dont know... :confused: vba doesnt seem to run this code. im always getting an error "Run time error '-2147217900 (80040e14)': Automation error"
I get this error everytime i add the sum([acdcalls]) and the Group by clause

Code:
Set rs = conn.Execute("SELECT [row_date],[split],[loc_id], sum([acdcalls]) FROM [C3_Analytics].[CMS].[hagent] Where Month([row_date])=3 AND Year([row_date])=2015 Group by [row_date],[split],[loc_id];")
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,744
Where do you get this error? on which line?

I doubt it'll matter, but try
Rich (BB code):
SELECT [row_date], 
       [split], 
       [loc_id], 
       Sum([acdcalls]) Calls
FROM   [C3_Analytics].[CMS].[hagent] 
WHERE  Month([row_date]) = 3 
       AND Year([row_date]) = 2015 
GROUP  BY [row_date], 
          [split], 
          [loc_id];
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Have you tried the query manually in the database?
 

jnmad0127

New Member
Joined
Mar 24, 2015
Messages
5
@kyle123 - no still doesnt make the vba code work...
@Norie - Yes, the query works in SQL server management studio but unfortunately when i place it in a VBA code it doesnt work, im thinking it might be missing something that i need to do to be able to use the sum and group by clause..

and im really bad with VB....
 

jnmad0127

New Member
Joined
Mar 24, 2015
Messages
5
I think it is this line.

Code:
Set rs = conn.Execute("SELECT [row_date],[split],[loc_id], sum([acdcalls]) FROM [C3_Analytics].[CMS].[hagent] Where Month([row_date])=3 AND Year([row_date])=2015 Group by [row_date],[split],[loc_id]")

Since after I placed "sum([acdcalls])" and the "Group by [row_date],[split],[loc_id]", im suddenly getting an error.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,259
Messages
5,836,284
Members
430,415
Latest member
jeremyweck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top