Overflow Error

[Dylan Snyder]

I'm making a query with a calculated field from three other fields, Order Qty, Case Pack, and Case Cube. Here is the formula for the calculated field:

NZ((([Purchase Order Detail]![Order Qty]/[Item Master]![Case Pack])*[Item Master]![Case Cube])/35.3147,0)

And here are the datatypes for the fields
Order Qty= Number, Double
Case Pack= Number, Decimal
Case Cube= Number, Decimal


I keep getting an overflow error. It seems like this should work, but obviously there is something I'm not seeing. Any one see a solution?

 

[jdraw]

There are no syntax errors in the statement. You didn't say what values you were using when the error occurred.

I mocked up a simple 1 table 1 record test using

Debug.Print Nz(((rs!orderQty / rs!Pack) * rs!cube) / 35.3147, 0)

with
orderqty = 23
pack = 135.755 precision 18, scale 3, decimal places 3
cube=23.345 precision 18, scale 3, decimal places 3

and received no error.

 

[pbaldy]

Are there any zeros or nulls in the data? They could cause a divide-by-zero error that I've seen called an overflow error.

 

[Dylan Snyder]

AHA! the NZ only changes null values, not zeroes. That's what I was missing. thanks

 

[Dylan Snyder]

wait, and replaces them with more zeroes. What the heck was I thinking that would solve my divide by zero error I was getting? argh. I appreciate the help.

 

[pbaldy]

One way around nulls/zeros is this type of thing:

IIf(Nz(Field2,0)=0,0,Field1/Field2)

 

[Dylan Snyder]

thanks, I'll have to modify it slightly because I have two potential zero values in the denominator but I will keep this in my bag of tricks. To be honest. I knew very little about access and have been having to play catch up. I appreciate your help on these problems

 

[pbaldy]

No problem; good luck with the project.