problem of #NUM! when using CInt with Nz

[Ramnik]

Hello everyone,
I have a query in which i use Nz function to convert null to 0 .
But further i need this value in another query as a number so I use it as
CInt(Nz(Sum([QtyReceived]),0))
group by expression

Is it correct.????

Upto some point it worked well and still working in many queries. But today when i entered a new value in my table it produces a #NUM! error in this field for that row only. and my query produces a overflow error.

What is the correct way to do it ????
Thankssss.

 

[pr2-eugin]

Try CLng instead. As it is clearly an Overflow, Long would be able to accommodate this !

[B]CLng[/B](Nz(Sum([QtyReceived]), 0))

 

[Ramnik]

Thanks a lot it worked .
But could you please tell me the reason. I have checked the table design and there is a field size (long integer) . Was this the reason ????
And why this error didn't occur before????

Thanks a lot.

 

[gemma-the-husky]

just to jump in.

integer is 2 bytes (16 bits), and has a range of 65536 values (2^16), which goes from -32768 to +32767 (I think)

longint is 4 bytes (32bits) and has a range of 2^32 values, therefore goes from approx. -2billion to +2billion, and therefore can accommodate much bigger values without an overflow

 

[Ramnik]

Thanks Brother. Yes i can see as my query takes more values and added that up the quantity becomes 34800 which is more than 32767. thats why it gives #NUM! error and so "overflow".
I changed it to long int and its working like a charms. Thanks guys.