Overflow error from select query

[Guest]

I am getting the error overflow when I create the following expresssion in a query

kk: ( [a] - ) / ( [c] -

I will let me calc ( [a] - ) or ( [c] - ) but I cannot divide the

Where a,b and c are retrieved from fields of type double and the original values only go to 3 decimals..

I have read this is caused when the numbers are out on the data types range, however I am already using double so what is the next step
n.b. I am running Access 2000, SP3 with Jet 4SP8 updat

Barr

 

[Allen Browne]

Access is not good at understanding the types of calculated fields.

Try typecasting one half of the expression into a double before division:
kk: CDbl( [a] - ) / ( [c] - )

CDbl() will fail with nulls.
If b and c are the same, you could get division-by-zero.
If b and c are almost the same, it may be possible to overflow a double?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am getting the error overflow when I create the following expresssion in a query.

kk: ( [a] - ) / ( [c] - )

I will let me calc ( [a] - ) or ( [c] - ) but I cannot divide the 2


Where a,b and c are retrieved from fields of type double and the original

values only go to 3 decimals...

I have read this is caused when the numbers are out on the data types

range, however I am already using double so what is the next step.

 

[Gary Walter]

I am getting the error overflow when I create the following expresssion in a query.

kk: ( [a] - ) / ( [c] - )

I will let me calc ( [a] - ) or ( [c] - ) but I cannot divide the 2


Where a,b and c are retrieved from fields of type double and the original values only go to 3 decimals...

I have read this is caused when the numbers are out on the data types range,

however I am already using double so what is the next step.

n.b. I am running Access 2000, SP3 with Jet 4SP8 update

Hi Barry,

You just cannot do simple subtractions and division
with type double fields in Access.

Please read Luke Chung's excellent paper on FMS website:
"When Access Math Doesn't Add Up"

http://www.fmsinc.com/tpapers/math/index.html

If you do not have "a lot of data", you could do

kk: IIF(CDec([c] & 0) - CDec( & 0) = 0, 0,
CDec([a] & 0) - CDec( & 0)/CDec([c] & 0) - CDec([d] & 0))

CDec() does like NULLs, so can use "[c] & 0"

?CDec(null & 0)
0
?CDec(1.112 & 0)
1.112

or use the NZ function.

If I had "a lot of data", I might just write a function
that I feed the 4 fields and try to optimize it.

Please respond back if I have misunderstood.

Good luck,

Gary Walter

 

[Gary Walter]

Sorry...I forgot to wrap subtractions in
parentheses...

try this instead:

kk: IIF(CDec([c] & 0) - CDec( & 0) = 0, 0,
(CDec([a] & 0) - CDec( & 0))/(CDec([c] & 0) - CDec([d] & 0)))

I am getting the error overflow when I create the following expresssion in a query.

kk: ( [a] - ) / ( [c] - )

I will let me calc ( [a] - ) or ( [c] - ) but I cannot divide

the

2


Where a,b and c are retrieved from fields of type double and the original values only go to 3 decimals...

I have read this is caused when the numbers are out on the data types range,

however I am already using double so what is the next step.

n.b. I am running Access 2000, SP3 with Jet 4SP8 update

Hi Barry,

You just cannot do simple subtractions and division
with type double fields in Access.

Please read Luke Chung's excellent paper on FMS website:
"When Access Math Doesn't Add Up"

http://www.fmsinc.com/tpapers/math/index.html

If you do not have "a lot of data", you could do

kk: IIF(CDec([c] & 0) - CDec( & 0) = 0, 0,
CDec([a] & 0) - CDec( & 0)/CDec([c] & 0) - CDec([d] & 0))

CDec() does like NULLs, so can use "[c] & 0"

?CDec(null & 0)
0
?CDec(1.112 & 0)
1.112

or use the NZ function.

If I had "a lot of data", I might just write a function
that I feed the 4 fields and try to optimize it.

Please respond back if I have misunderstood.

Good luck,

Gary Walter

 

[Gary Walter]

and...

CDec() does like NULLs, so can use "[c] & 0"

should read

CDec() does NOT like NULLs, so can use "[c] & 0"