Disable error message

[AdamP]

I am running a stored procedure from VBA code that creates a table using a group-by query. When I run the stored proc, I get following message: "Warning: Null value is eliminated by an aggregate or other SET operation." How do I disable this message, since it stops the code? I already have the statement 'DoCmd.SetWarnings False' in the VBA code.

Thank you.

 

[vongrunt]

That happens when query performs SUM/MAX/MIN/whatever and encounters NULL value(s). Because NULL means "unknown", result of such operations is theoretically also "unknown".

In practice this strict rule is rather meaningless - NULL in aggregates can be almost always safely ignored. Two choices:

- rewrite query so that NULL values never appear in aggregates
- use SET ANSI WARNINGS OFF before and ON after query



------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rac2]

Depending on the query and the data you can sometimes eliminate the warning by adding a condition to exclude rows with NULL values from the aggregate.

SELECT m.species, SUM(m.weight) AS "ChimpanzeeWeight"
FROM Barrels b
LEFT JOIN Monkeys m ON m.barrel_id = b.barrel_id
           AND m.species = 'troglodyte'
           AND m.barrel_id IS NOT NULL

If there are no Chimpanzees in a particular barrel the row will exist because of the outer join but will have NULLs for all of the columns for the monkeys table. Adding the IS NOT NULL condition eliminates the warning. The query gives the same answer either way.

 

[AdamP]

The set ansi_warnings off worked. That was exaclty what I was looking for. Thanks!