I am trying to run this formula (=([NonShippable]/[Qty_Needed])*-[OnHandBalance]) but for some reason I am getting #Error for any fields that do not have anything in them. Any suggestions? I am guessing it has to do with Nulls.

Try something like the following:

(nz([NonShippable])/nz([Qty_Needed]))*-nz([OnHandBalance]))

The nz() function replaces nulls with a zero.

If you use it in a query you need to add the value to use if it does find a null:

(nz([NonShippable],0)/nz([Qty_Needed],0))*-nz([OnHandBalance],0))

Hope this helps - :)

Neither one of them worked. I am not using it in a query. I am just creating the formula. Adding a new field in the report.

Try doing something a bit more simple and work up. Try something like:

nz([NonShippable],0)

and if that works then maybe:

nz([NonShippable],0)/nz([Qty_Needed],0)

???

When I just put this in =nz([NonShippable]) it works properly. I go any further and those #Errors come back.

What about just:

=nz([Qty_Needed])

???

Ah... I see you could get a div by zero error.

What about:

=(nz([NonShippable],0)/nz([Qty_Needed],1))*-nz([OnHandBalance],0))

???

Still not working. Very weird and stressful.

This is one way that I got it to pass correctly but the numbers are far from being correct:

=nz((([NonShippable]," ")/nz([Qty_Needed])*-nz([OnHandBalance]))

Are all of the fields numeric data types or is NonShippable a text field?

Thanks for your help. I figured it out. I needed an IIF Statement in there.

Really...?

Oh well, glad you have it workin' :)