Overflow Error

[duesouth]

I've created a calculated field which is the average score in various exams (called "Average").

I've then created another field: Average 0 Dec Places: CLng([Average]) to set that to a long integer with 0 decimal places.

I then create a relationship between the [Average 0 Dec Places] and a table which contains the number (long integer 0 decimal places) and the Grade with that - i.e.

1 = U
2 = U
up to 100 = A

When I run a query to bring back the corresponding grade to the [Average] field, it works - but then after a couple of seconds I get an Overflow error and upon clicking OK, all the returned values become #Name?

I'm not sure if I'm going down the right path and just need a tweak - or I'm miles away in terms of trying to get around this problem.

 

[vbaInet]

Use CDbl()

 

[duesouth]

Use CDbl()

Still getting the overflow error.....

 

[vbaInet]

Your code is probably endlessly calculating. Can we see your db?

 

[duesouth]

Tried a lookup expression:

Spec A Current: DLookUp("Grade","Spec A Current Lookup","Mark = " & [Spec A Dbl])

And it worked!

Thanks.

 

[vbaInet]

I was just writing this message before you replied and I was going to advise using a DLookup(). I think it's still worth me showing you the message:

"I can't delve into the structure of your tables but I have reason to believe that it isn't properly normalized. My guess is it has something to do with your structure and relationships.

Anyway, to solve your problem use a function as your lookup instead. Or if you still want to use the table, use a DLookup() function in your query, and yes it will run slower than it currently is."

Attached shows how the function is used (and the function is in a Module).

 

[gemma-the-husky]

overflow is either

divide by 0, or maybe a number too big to be a long (ie >2billion approx)

Alternatively, I am not sure, but if an average has a floating value, then trying to do a

clng(48.6) may produce an overflow error immediately.

 

[vbaInet]

Alternatively, I am not sure, but if an average has a floating value, then trying to do a

clng(48.6) may produce an overflow error immediately.

That got me thinking. CLng() would actually help to get rid of the error.

duesouth: If you change this calculation:

Spec A Sum: CLng([BLY1AP])+CLng([BLY1BP])+CLng([CHY1AP])+CLng([CHY1BP])+CLng([PHY1AP])+CLng([PHY1BP])+CLng([SCYC_UMS])

It doesn't "overflow" because the other calculations based on this result are also casted to Long.

Also, you will notice in the db attached that I used this:

Avg Spec A: [Spec A Sum]/IIf(CLng(nz([Spec A Count],0))=0,1,[spec a count])