Arithmetic Error -- Converting Expression to Int -- Weird Behavior

[Chris C]

I'm experiencing the nuisance that is the "arithmetic overflow converting
expression to int" error, but I'm not seeing this error consistently enough
to think there's something wrong with my code.

Here's the snippet:
select round((sum(gu)/50000),2) as gu_aal,
round((sqrt(((50000*sum(gu_square))-square(sum(gu)))/(50000*49999))),2) as
gu_stdev

The first field is basically an average, while the second is a specialized
standard deviation.

I use a bit of VBA to extract the result of this query. So, when I hit the
play button to let the function run, the error pops up and stops my function
dead. But, when I hit F8 and step through the function, it works perfectly.
To me, that makes no sense. The code never changes, so why would it run using
one method but not the other?

Can anyone shed some light on this problem?

 

[Tim Williams]

Why not use a long ?
Without seeing the vba it's difficult to guess why F8 makes a difference.

Tim

 

[Chris C]

Here is the full snippet:

' --------- Mean Aggregate GU AAL
If intYear = 50000 Then
query = "select round((sum(gu)/50000),2) as gu_aal,
round((sqrt(((50000*sum(gu_square))-square(sum(gu)))/(50000*49999))),2) as
gu_stdev "
Else
query = "select round((sum(gu)/10000),2) as gu_aal,
round((sqrt(((10000*sum(gu_square))-square(sum(gu)))/(10000*9999))),2) as
gu_stdev "
End If
query = query & "from mean_gu_agg_aal_working"

resultset.ActiveConnection = cnPubs
resultset.Open query

Sheet6.Range("D5:E5").CopyFromRecordset resultset

resultset.Close

-- Does that help?

 

[Tim Williams]

When something works when stepping through but doesn't when run directly it
often means there's some part of the process which is running asynchronously
with the rest. I'm not sure what that would be in your case though.

Where is the error coming from - ie. which line ? Is it coming from VBA or
is it a database error ?

Tim