Run-Time Error '6'

[AJ]

I am running this in VBA using a dlookup with it accessing the access query:
SELECT (1 / Sum(1)) * Sum((X - (SELECT Avg(A.X) FROM postcorrel AS A)) *
(Y - (SELECT Avg(A.Y) FROM postcorrel AS A))) / ((SELECT StDevP(A.X) FROM
postcorrel AS A) * (SELECT StDevP(A.Y) FROM postcorrel AS A)) AS correl
FROM postcorrel

This query returns a correlation value. I have never had issue with this
query but now when I run it with approx. 5000 records I get a:
Run-Time Error '6' overflow ERROR

CAN ANYONE PLEASE HELP AND TELL ME WHAT THIS IS AND HOW TO FIX IT.

Thanks, AJ

 

[boblarson]

To what datatype are you assigning the DLookup value? You might not have the
right datatype to fit.

--
Bob Larson
Access World Forums Administrator

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[James A. Fortune]

I am running this in VBA using a dlookup with it accessing the access query:
SELECT (1 / Sum(1)) * Sum((X - (SELECT Avg(A.X) FROM postcorrel AS A)) *
(Y - (SELECT Avg(A.Y) FROM postcorrel AS A))) / ((SELECT StDevP(A.X) FROM
postcorrel AS A) * (SELECT StDevP(A.Y) FROM postcorrel AS A)) AS correl
FROM postcorrel

This query returns a correlation value. I have never had issue with this
query but now when I run it with approx. 5000 records I get a:
Run-Time Error '6' overflow ERROR

CAN ANYONE PLEASE HELP AND TELL ME WHAT THIS IS AND HOW TO FIX IT.

Thanks, AJ

I would first try running the same query except using the actual values
returned by the subqueries in place of them. I might also separate out
individual expressions from parts of the query, such as Sum(X -
24.3334243), to isolate which variable is causing problems. I might
even do the summations in VBA with a Recordset based on postcorrel so
that I can see at what point (value) the overflow is reached and perhaps
find a clue there. Finally, depending on what the individual
expressions are doing I might rearrange the order of mathematical
operations to delay an overflow for as long as possible. It might help
to know that SQL expressions are often evaluated using the Decimal Type.
I can't tell what the problem is from your SQL alone. Sorry.

James A. Fortune
(e-mail address removed)

You're good, but you did miss the problem caused by the thermocouple
junction mass induced temperature lag not being able to handle the
shortened time scale from the similarity conditions. -- Dr. Gil Wedekind