Access Query works in Access but doesn't work in Excel VBA via ADODB

[Ashton2290]

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

 

[Ashton2290]

Thanks for you input james_lankford I think I figured out the issue,
When I enter any number value (as string or integer) in the where clause the query doesn't work, even when I hardcode it.
"select a.str " & _
"from [Store Comp Status Master] a " & _
"where a.str = 701;"

Is there a workaround?

 

[xenou]

Are you absolutely sure the datatype is number in the database? Is it specifically Number/Long Integer? This is not something I have ever heard of being a problem before.

 

[Ashton2290]

Are you absolutely sure the datatype is number in the database? Is it specifically Number/Long Integer? This is not something I have ever heard of being a problem before.

It is specifically a double.

 

[xenou]

It should be long integer. You aren't using store numbers that have decimals, are you?

 

[Ashton2290]

It should be long integer. You aren't using store numbers that have decimals, are you?

It is a production database at my company but I do have admin rights to it. So I can change it if necessary
BTW I changed the table and it works!!!
Thank you so much guys!

 

[xenou]

cool beans.