Ashton2290
New Member
- Joined
- Jun 29, 2017
- Messages
- 9
Hello Everyone,
First time poster, the following query works just fine in access:
However, when I try to connect it to VBA it doesn't work: I get a runtime error '2147217887 (80040e21)': Automation Error
Code and query below
Select a.str, a.name, b.dpt, c.desc, sum(b.[Sales TY]) AS [Sales TY], sum (b.[Sales LY]) AS [Sales LY],
[Sales TY]/[Sales LY]-1 AS [% Change $ Sales],
avg(f.[Ending Retail TY]) AS [Dollar Inv TY],
avg(f.[Ending Retail LY]) AS [Dollar Inv LY],
[Dollar Inv TY]/[Dollar Inv LY]-1 AS [% Change Dollar Inv],
sum(d.TW_SLS_TY) AS [FP UNIT SLS TY],
sum(e.TW_SLS_LY) AS [FP UNIT SLS LY],
[FP UNIT SLS TY]/[FP UNIT SLS LY]-1 AS [% Change FP Unit Sls],
avg(e.TW_INV_TY) AS [FP UNIT INV TY],
avg(e.TW_INV_LY) AS [FP UNIT INV LY],
[FP UNIT INV TY]/[FP UNIT INV LY]-1 AS [% Change FP Unit Inv],
[FP UNIT INV TY]/[FP UNIT SLS TY]*(9-7+1) AS [SSR TY],
[FP UNIT INV LY]/[FP UNIT SLS LY]*(9-7+1) AS [SSR LY],
Sum(e.DISTRO) AS DISTRO, Sum(d.TW_SLS_TY) AS [MD UNIT SLS TY],
Sum(d.TW_SLS_LY) AS [MD UNIT SLS LY],
[MD UNIT SLS TY]/[MD UNIT SLS LY]-1 AS [% Change MD Unit Sls],
Avg(d.TW_INV_TY) AS [MD UNIT INV TY],
Avg(d.TW_INV_LY) AS [MD UNIT INV LY],
[MD UNIT INV TY]/[MD UNIT INV LY]-1 AS [% Change MD Unit Inv]
from (((([Store Comp Status Master] a
inner join [Dlr F19] b on a.str = b.str)
inner join [Department Name Master] c on b.dpt = c.dpt_no)
left join [MD Summ F19] d on b.week = d.week and b.dpt = d.dpt and b.str = d.str)
left join [Dpt Summ F19] e on b.week = e.wk_no and b.dpt = e.dpt_no and b.str = e.str)
left join [Dlr Inv F19] f on b.week = f.week and b.dpt = f.dpt and b.str = f.str
where a.str = 701 and b.week >= 7 and b.week <= 9
group by a.str, a.name, b.dpt, c.desc
Sub SPM()
Dim SPMConn As ADODB.Connection
Dim SPMData As ADODB.Recordset
Dim SPMField As ADODB.Field
Set SPMConn = New ADODB.Connection
Set SPMData = New ADODB.Recordset
If Sheet1.Range("A1").Value <> "" Then
Clear
End If
SPMConn = Winners
SPMConn.Open
With SPMData
.ActiveConnection = SPMConn
.Source = reportSource
.Open
End With
Range("A1").Select
For Each SPMField In SPMData.Fields
ActiveCell.Value = SPMField.Name
ActiveCell.Offset(0, 1).Select
Next SPMField
Range("A2").CopyFromRecordset SPMData
SPMData.Close
SPMConn.Close
End Sub
Function:
Public Function DeptQuery() As String
Dim firstWeek As String
Dim lastWeek As String
Dim store As String
Dim query As String
Sheet1.Select
firstWeek = Range("BG1").Value
lastWeek = Range("BG2").Value
store = Range("BG3").Value
query = "Select a.str, a.name, b.dpt, c.desc, sum(b.[Sales TY]) AS [Sales TY], sum (b.[Sales LY]) AS [Sales LY], " & _
"[Sales TY]/[Sales LY]-1 AS [% Change $ Sales], avg(f.[Ending Retail TY]) AS [Dollar Inv TY], avg(f.[Ending Retail LY]) AS [Dollar Inv LY], " & _
"[Dollar Inv TY]/[Dollar Inv LY]-1 AS [% Change Dollar Inv], sum(d.TW_SLS_TY) AS [FP UNIT SLS TY], sum(e.TW_SLS_LY) AS [FP UNIT SLS LY], " & _
"[FP UNIT SLS TY]/[FP UNIT SLS LY]-1 AS [% Change FP Unit Sls], avg(e.TW_INV_TY) AS [FP UNIT INV TY], avg(e.TW_INV_LY) AS [FP UNIT INV LY], " & _
"[FP UNIT INV TY]/[FP UNIT INV LY]-1 AS [% Change FP Unit Inv], [FP UNIT INV TY]/[FP UNIT SLS TY]*(9-7+1) AS [SSR TY], [FP UNIT INV LY]/[FP UNIT SLS LY]*(9-7+1) AS [SSR LY], " & _
"Sum(e.DISTRO) AS DISTRO, Sum(d.TW_SLS_TY) AS [MD UNIT SLS TY], Sum(d.TW_SLS_LY) AS [MD UNIT SLS LY], [MD UNIT SLS TY]/[MD UNIT SLS LY]-1 AS [% Change MD Unit Sls], Avg(d.TW_INV_TY) AS [MD UNIT INV TY], " & _
"Avg(d.TW_INV_LY) AS [MD UNIT INV LY], [MD UNIT INV TY]/[MD UNIT INV LY]-1 AS [% Change MD Unit Inv] " & _
"from (((([Store Comp Status Master] a inner join [Dlr F19] b on a.str = b.str) " & _
"inner join [Department Name Master] c on b.dpt = c.dpt_no) left join [MD Summ F19] d on b.week = d.week and b.dpt = d.dpt and b.str = d.str) " & _
"left join [Dpt Summ F19] e on b.week = e.wk_no and b.dpt = e.dpt_no and b.str = e.str) left join [Dlr Inv F19] f on b.week = f.week and b.dpt = f.dpt and b.str = f.str " & _
"where a.Str = " & store & " And b.week >= " & firstWeek & " And b.week <= " & lastWeek & _
" group by a.str, a.name, b.dpt, c.desc"
DeptQuery = query
End Function
First time poster, the following query works just fine in access:
However, when I try to connect it to VBA it doesn't work: I get a runtime error '2147217887 (80040e21)': Automation Error
Code and query below
Select a.str, a.name, b.dpt, c.desc, sum(b.[Sales TY]) AS [Sales TY], sum (b.[Sales LY]) AS [Sales LY],
[Sales TY]/[Sales LY]-1 AS [% Change $ Sales],
avg(f.[Ending Retail TY]) AS [Dollar Inv TY],
avg(f.[Ending Retail LY]) AS [Dollar Inv LY],
[Dollar Inv TY]/[Dollar Inv LY]-1 AS [% Change Dollar Inv],
sum(d.TW_SLS_TY) AS [FP UNIT SLS TY],
sum(e.TW_SLS_LY) AS [FP UNIT SLS LY],
[FP UNIT SLS TY]/[FP UNIT SLS LY]-1 AS [% Change FP Unit Sls],
avg(e.TW_INV_TY) AS [FP UNIT INV TY],
avg(e.TW_INV_LY) AS [FP UNIT INV LY],
[FP UNIT INV TY]/[FP UNIT INV LY]-1 AS [% Change FP Unit Inv],
[FP UNIT INV TY]/[FP UNIT SLS TY]*(9-7+1) AS [SSR TY],
[FP UNIT INV LY]/[FP UNIT SLS LY]*(9-7+1) AS [SSR LY],
Sum(e.DISTRO) AS DISTRO, Sum(d.TW_SLS_TY) AS [MD UNIT SLS TY],
Sum(d.TW_SLS_LY) AS [MD UNIT SLS LY],
[MD UNIT SLS TY]/[MD UNIT SLS LY]-1 AS [% Change MD Unit Sls],
Avg(d.TW_INV_TY) AS [MD UNIT INV TY],
Avg(d.TW_INV_LY) AS [MD UNIT INV LY],
[MD UNIT INV TY]/[MD UNIT INV LY]-1 AS [% Change MD Unit Inv]
from (((([Store Comp Status Master] a
inner join [Dlr F19] b on a.str = b.str)
inner join [Department Name Master] c on b.dpt = c.dpt_no)
left join [MD Summ F19] d on b.week = d.week and b.dpt = d.dpt and b.str = d.str)
left join [Dpt Summ F19] e on b.week = e.wk_no and b.dpt = e.dpt_no and b.str = e.str)
left join [Dlr Inv F19] f on b.week = f.week and b.dpt = f.dpt and b.str = f.str
where a.str = 701 and b.week >= 7 and b.week <= 9
group by a.str, a.name, b.dpt, c.desc
Sub SPM()
Dim SPMConn As ADODB.Connection
Dim SPMData As ADODB.Recordset
Dim SPMField As ADODB.Field
Set SPMConn = New ADODB.Connection
Set SPMData = New ADODB.Recordset
If Sheet1.Range("A1").Value <> "" Then
Clear
End If
SPMConn = Winners
SPMConn.Open
With SPMData
.ActiveConnection = SPMConn
.Source = reportSource
.Open
End With
Range("A1").Select
For Each SPMField In SPMData.Fields
ActiveCell.Value = SPMField.Name
ActiveCell.Offset(0, 1).Select
Next SPMField
Range("A2").CopyFromRecordset SPMData
SPMData.Close
SPMConn.Close
End Sub
Function:
Public Function DeptQuery() As String
Dim firstWeek As String
Dim lastWeek As String
Dim store As String
Dim query As String
Sheet1.Select
firstWeek = Range("BG1").Value
lastWeek = Range("BG2").Value
store = Range("BG3").Value
query = "Select a.str, a.name, b.dpt, c.desc, sum(b.[Sales TY]) AS [Sales TY], sum (b.[Sales LY]) AS [Sales LY], " & _
"[Sales TY]/[Sales LY]-1 AS [% Change $ Sales], avg(f.[Ending Retail TY]) AS [Dollar Inv TY], avg(f.[Ending Retail LY]) AS [Dollar Inv LY], " & _
"[Dollar Inv TY]/[Dollar Inv LY]-1 AS [% Change Dollar Inv], sum(d.TW_SLS_TY) AS [FP UNIT SLS TY], sum(e.TW_SLS_LY) AS [FP UNIT SLS LY], " & _
"[FP UNIT SLS TY]/[FP UNIT SLS LY]-1 AS [% Change FP Unit Sls], avg(e.TW_INV_TY) AS [FP UNIT INV TY], avg(e.TW_INV_LY) AS [FP UNIT INV LY], " & _
"[FP UNIT INV TY]/[FP UNIT INV LY]-1 AS [% Change FP Unit Inv], [FP UNIT INV TY]/[FP UNIT SLS TY]*(9-7+1) AS [SSR TY], [FP UNIT INV LY]/[FP UNIT SLS LY]*(9-7+1) AS [SSR LY], " & _
"Sum(e.DISTRO) AS DISTRO, Sum(d.TW_SLS_TY) AS [MD UNIT SLS TY], Sum(d.TW_SLS_LY) AS [MD UNIT SLS LY], [MD UNIT SLS TY]/[MD UNIT SLS LY]-1 AS [% Change MD Unit Sls], Avg(d.TW_INV_TY) AS [MD UNIT INV TY], " & _
"Avg(d.TW_INV_LY) AS [MD UNIT INV LY], [MD UNIT INV TY]/[MD UNIT INV LY]-1 AS [% Change MD Unit Inv] " & _
"from (((([Store Comp Status Master] a inner join [Dlr F19] b on a.str = b.str) " & _
"inner join [Department Name Master] c on b.dpt = c.dpt_no) left join [MD Summ F19] d on b.week = d.week and b.dpt = d.dpt and b.str = d.str) " & _
"left join [Dpt Summ F19] e on b.week = e.wk_no and b.dpt = e.dpt_no and b.str = e.str) left join [Dlr Inv F19] f on b.week = f.week and b.dpt = f.dpt and b.str = f.str " & _
"where a.Str = " & store & " And b.week >= " & firstWeek & " And b.week <= " & lastWeek & _
" group by a.str, a.name, b.dpt, c.desc"
DeptQuery = query
End Function