- Joined
- Sep 1, 2013
- Messages
- 1
- Reaction score
- 0
Hi,
I'm quite new to vba and sql and I am having a little trouble importing some data from my database query into excel via vba.
I have the following sql generating a query in Access which displays the query results no problem.
I am then trying to use vba to pull a record dataset from the query generated using the below sql statement via vba into excel:
When running the above code I get the following error:
"You tired to execute a query that does not include the specified expression 'IIf(IIf(Is Null,0,)=0,"0", IIf([Total MB Product Shoot Complete]=0, "0",[Total MB Product Shoot Complete]/[Total MB Assigned]))' as part of an aggregate function."
If anyone can point me in the right direction, or if I am doing this completely wrong I would be very grateful!
I'm quite new to vba and sql and I am having a little trouble importing some data from my database query into excel via vba.
I have the following sql generating a query in Access which displays the query results no problem.
Code:
SELECT tblSection.Section_Name, IIf(Sum(qry_Total_Assigned_MB.[Total Assigned Main Book]) Is Null,0,Sum(qry_Total_Assigned_MB.[Total Assigned Main Book])) AS [Total MB Assigned], IIf(Sum([qry_Total_Shoot_Complete_MB].[Total MB Product Shoot Complete]) Is Null,0,Sum([qry_Total_Shoot_Complete_MB].[Total MB Product Shoot Complete])) AS [Total MB Product Shoot Complete], IIf(Sum(qry_Total_Assigned_MidB.[Total Assigned Mid Book]) Is Null,0,Sum(qry_Total_Assigned_MidB.[Total Assigned Mid Book])) AS [Total MidB Assigned], IIf(Sum([qry_Total_Shoot_Complete_MidB].[Total MidB Product Shoot Complete]) Is Null,0,Sum([qry_Total_Shoot_Complete_MidB].[Total MidB Product Shoot Complete])) AS [Total MidB Product Shoot Complete], IIf(Sum([qry_Total_Assigned_ECA].[Total Assigned ECA]) Is Null,0,Sum([qry_Total_Assigned_ECA].[Total Assigned ECA])) AS [Total ECA Assigned], IIf(Sum([qry_Total_Assigned_ECOMM].[Total Assigned ECOMM]) Is Null,0,Sum([qry_Total_Assigned_ECOMM].[Total Assigned ECOMM])) AS [Total ECOMM Assigned], IIf(Sum([qry_Total_Assigned_Marketing].[Total Assigned Marketing]) Is Null,0,Sum([qry_Total_Assigned_Marketing].[Total Assigned Marketing])) AS [Total Marketing Assigned], IIf([Total MB Assigned]=0,"0",IIf([Total MB Product Shoot Complete]=0,"0",([Total MB Product Shoot Complete])/([Total MB Assigned]))) AS [Percentage MB Complete]
FROM ((((((tblSection LEFT JOIN qry_Total_Assigned_MB ON tblSection.Section_Name = qry_Total_Assigned_MB.Section_Name) LEFT JOIN qry_Total_Assigned_MidB ON tblSection.Section_Name = qry_Total_Assigned_MidB.Section_Name) LEFT JOIN qry_Total_Assigned_ECA ON tblSection.Section_Name = qry_Total_Assigned_ECA.Section_Name) LEFT JOIN qry_Total_Assigned_ECOMM ON tblSection.Section_Name = qry_Total_Assigned_ECOMM.Section_Name) LEFT JOIN qry_Total_Assigned_Marketing ON tblSection.Section_Name = qry_Total_Assigned_Marketing.Section_Name) LEFT JOIN qry_Total_Shoot_Complete_MB ON tblSection.Section_Name = qry_Total_Shoot_Complete_MB.Section_Name) LEFT JOIN qry_Total_Shoot_Complete_MidB ON tblSection.Section_Name = qry_Total_Shoot_Complete_MidB.Section_Name
GROUP BY tblSection.Section_Name;I am then trying to use vba to pull a record dataset from the query generated using the below sql statement via vba into excel:
Code:
strSQL = "SELECT [Total MB Assigned] FROM qry_MI_Total_Assigned WHERE [Section_Name] = '" & fnSection_Name & "' GROUP BY [Section_Name] "When running the above code I get the following error:
"You tired to execute a query that does not include the specified expression 'IIf(IIf(Is Null,0,)=0,"0", IIf([Total MB Product Shoot Complete]=0, "0",[Total MB Product Shoot Complete]/[Total MB Assigned]))' as part of an aggregate function."
If anyone can point me in the right direction, or if I am doing this completely wrong I would be very grateful!