combo box through to SQL

[DrJimmy]

I'm using a form with combo boxes to apply filters on an append query. I have used the following SQL instruction that works if I run as a query but if I run in VB using CurrentDb.Execute(SQL string) I get the following error.

Run time error '3122':

You tried to execute a query that does not include the specified expression 'Channel' as part of an aggregate function.

The SQl is

INSERT INTO grp1Source ( Channel, [SumOfGross Margin] )SELECT Z0010_select_only_sales.Channel, Sum(Z0010_select_only_sales.[Gross Margin]) AS [SumOfGross Margin] FROM Z0010_select_only_sales

WHERE (((Z0010_select_only_sales.company) = forms!form2!cpyCombo OR forms!form2!cpyCombo IS NULL) )
GROUP BY Z0010_select_only_sales.Channel

HAVING ((Z0010_select_only_sales.Channel) = forms!form2!cnlCombo OR forms!form2!cnlCombo IS NULL);

There are other filters in the WHERE statement but I have removed them on here to make it easier to read and they are just repeats of the first one with different fields etc.

What am i doing wrong?!

 

[ajetrumpet]

Run time error '3122':

You tried to execute a query that does not include the specified expression 'Channel' as part of an aggregate function.

The SQl is

INSERT INTO grp1Source ( Channel, [SumOfGross Margin] )SELECT Z0010_select_only_sales.Channel, Sum(Z0010_select_only_sales.[Gross Margin]) AS [SumOfGross Margin] FROM Z0010_select_only_sales

WHERE (((Z0010_select_only_sales.company) = forms!form2!cpyCombo OR forms!form2!cpyCombo IS NULL) )
GROUP BY Z0010_select_only_sales.Channel

HAVING ((Z0010_select_only_sales.Channel) = forms!form2!cnlCombo OR forms!form2!cnlCombo IS NULL);

It's probably telling you this because it's reading the first criteria clause that comes up (which is the WHERE clause). As far as I know, when there are groupings involved, the HAVING clause is the replacement for the WHERE clause. I doubt the program is even reading anything after the WHERE clause, because that's supposed to be the end! Put that criteria info into the HAVING clause instead...
_________________________________________________________________________________________________________________________________________________

INSERT INTO grp1Source ( Channel, [SumOfGross Margin] )SELECT Z0010_select_only_sales.Channel, Sum(Z0010_select_only_sales.[Gross Margin]) AS [SumOfGross Margin] FROM Z0010_select_only_sales

GROUP BY Z0010_select_only_sales.Channel

HAVING ((Z0010_select_only_sales.company) = forms!form2!cpyCombo OR forms!form2!cpyCombo IS NULL) AND <----- (The "AND" operator here, right?)
((Z0010_select_only_sales.Channel) = forms!form2!cnlCombo OR forms!form2!cnlCombo IS NULL);

 

[DrJimmy]

Getting there! It now comes up with

You tried to execute a query that does not include the specific expression '(Z0010_select_only_sales.company=forms!form2!cpyCombo or forms!form2!cpyCombo Is Null) AND (etc.......

The whole SQL is

sql_string = "INSERT INTO grp1Source ( Channel, [SumOfGross Margin] )SELECT Z0010_select_only_sales.Channel, Sum(Z0010_select_only_sales.[Gross Margin]) AS [SumOfGross Margin] FROM Z0010_select_only_sales GROUP BY Z0010_select_only_sales.Channel "

sql_where = "HAVING (((Z0010_select_only_sales.company) = forms!form2!cpyCombo OR forms!form2!cpyCombo IS NULL) AND ((Z0010_select_only_sales.area) = forms!form2!slspnCombo OR forms!form2!slspnCombo IS NULL) AND ((Z0010_select_only_sales.site) = forms!form2!wbsCombo OR forms!form2!wbsCombo IS NULL) AND ((Z0010_select_only_sales.month_name) = forms!form2!mthCombo OR forms!form2!mthCombo IS NULL) AND ((Z0010_select_only_sales.year) = forms!form2!yrCombo OR forms!form2!yrCombo IS NULL) AND ((Z0010_select_only_sales.Channel) = forms!form2!cnlCombo OR forms!form2!cnlCombo IS NULL));"

sql_execute = sql_string & sql_where

CurrentDb.Execute (sql_execute)

When I look at the value of sql_execute it does look like I'd expect it which is the above as one continuous SQL instruction.

 

[ajetrumpet]

Jimmy,

Any other fields involved in the query at all need to be in the SELECT clause, and grouped, because of the aggregate function.

I think you're going to have to get the rows that you need first, without the SUM function, and then append them to the new table using the function. Also, there is nothing wrong with the sql_execute string or the concatenation of the two strings that contribute to it.