I posted this question once before without a clear answer. I would
appreciate any advice I could get.
I am having a problem running a sub query in VBA. I trying to build a
where clause that will be inserted when I open the report.
I am getting a syntax error when I run it in VBA but not when I run it
in a query. Here is the problematic portion of my WHERE clause code:

(EXISTS(SELECT Sales.AgentID,Sum(Sales.Premium), AS SumOfPremium FROM
Sales Group BY Sales.AgentID HAVING (((Sum(Sales.Premium)) > =
500000))<>False))

In words this is supposed to pull the data where the sum of premium
for each agentID >= 500000. What syntax am I missing because I don't
seem to catch the error when it works in a query just fine. Thanks in
advance.

-WhathaveIdone?

WhathaveIdone? wrote:
Two things:
- the lack of a space between EXISTS and the opening parenthesis is
worrying, but might not be a problem. I would tuck a space in there just to
be sure
- the comma between Sum(Sales.Premium) and AS SumOfPremium will definitely
throw an error
Oh! I just saw a third problem: the space between > and = in that HAVING
clause

My personal preference is to eliminate as many parentheses from sql in VBA
code as possible to make it easier to find those syntax errors. The Query
Builder is great, but it really overdoes the parentheses. Also, with only a
single table in the FROM clause, there is no need to qualify the field
names - there is no ambiguity.

EXISTS (SELECT AgentID,Sum(Premium) AS SumOfPremium FROM
Sales Group BY AgentID HAVING (Sum(Premium) >= 500000)<>False)

And you can get rid of that <>False part, leaving:

EXISTS (SELECT AgentID,Sum(Premium) AS SumOfPremium FROM
Sales Group BY AgentID HAVING Sum(Premium) >= 500000)

This can be simplified even further. There is no need to have the
aggregation in the SELECT clause - you're not returning that to the main
query. And I think you don't even need the AgentID in the SELECT. This will
work:

EXISTS (SELECT * FROM Sales Group BY AgentID HAVING Sum(Premium) >= 500000)

A little easier to read, don't you think?

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote

my tables and got the following errors (Cannot group on fields selected with
'*').

Ron Paii wrote:
Yes, you're correct of course. The grouped by field needs to be in the
SELECT ... but nothing else.

EXISTS (SELECT AgentID FROM Sales Group BY AgentID HAVING Sum(Premium) >=
500000)

Without the GROUP BY, "SELECT *" would be correct.