SQL Query Doesnt work in Excel but same Query works in Access

Question

Hello team,

I am trying to run a SQL query in Excel VBA its not working. But same query works in Access database. Although query not prompting any error but it returns nothing.

excel version of query:-

"SELECT [Details1$].[Channel], [Details1$].[Sold to Customer], [Details1$].[BU Segment], [Details1$].[YR], " & _

"Sum(IIf([Details1$].[Qtr]='Q1',[Details1$]![Amt])) AS Q1, Sum(IIf([Details1$].[Qtr]='Q2', " & _

"[Details1$]![Amt])) AS Q2, Sum(IIf([Details1$].[Qtr]='Q3',[Details1$]![Amt])) AS Q3, Sum(IIf([Details1$].[Qtr]='Q4', " & _

"[Details1$]![Amt])) AS Q4, [Details1$].Channel " & _

"FROM [Details1$] " & _

"GROUP BY [Details1$].[Channel], [Details1$].[Sold to Customer], [Details1$].[BU Segment], [Details1$].YR, [Details1$].[Channel] " & _

"HAVING ((([Details1$].[BU Segment]) In ('Product','Services','MT')) AND (([Details1$].YR)='FY16') AND (([Details1$].[Channel])='HP') ) "

I have properly set data type for all the fields. But When I remove   (([Details1$].[Channel])='HP') ) this query works. I have  check field name are intact and data type is all set. But same query works perfectly in access database without removing  (([Details1$].[Channel])='HP') ) " this criteria.

Could anyone of help me and explain why there are so many limitation with Excel VBA-SQL. In past I have faced so many similar kind of problems with Excel VBA-SQL.

Answer 1

Hello Ricky,

Since you are using Excel query, I kindly request you to post this query in the following TechNet forum for better suggestion.

https://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel

Thank you.