Help needed with CR 8.5 formula- if blank or zero then.....

wgraphics used Ask the Experts™
Using CR 8.5
I am trying to modify a worksheet that has been in use for years. I am trying to get the correct Ext. Price.

The Ext. Price is the ShipQty X Unit Price. If the ShipQty is blank or zero, then the Ext. Price=Order Qty X Unit Price.

The formula I have in the report now works- as long as there is a ship quantity. If there is no ship qty then the formula craps out on me.

I hope the following keeps its formatting, I apologize if it does not.

Here is an example of one that DOES work:

Desc.      Order Qty     Ship Qty      Unit Price         UOM         Ext. Price
Letters     150                  150         .8667                     EA           $130      

Here is an example of one that does NOT work:
Desc.         Order Qty    Ship Qty          Unit Price        UOM       Ext. Price
Notepads     100                                         3.6500         EA

The ShipQty is a subreport. That is the only field in the subreport and this is what is consists of:
ShipQty: if isnull (Sum ({BBJOBSHL.shipquant})) then 0 else Sum ({BBJOBSHL.shipquant})

I am building the Ext. Price as a subreport as well. This is what I have in there now (but it doesn’t work if shipqty is blank or if it has 0):

Ext. Price:
if (sum({BBJOBSHL.shipquant}))= 0 then {@Quan_UnitPrice} else {@Sum_Unit}

{BBJTHEAD.QUAN}*{@Unit Price}

Sum ({BBJOBSHL.shipquant})*{@Unit Price}

I just can’t seem to get it to work if ShipQty is blank or zero….
Anyone have any ideas?
Thank you!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Try this
Add a formula to the subreport
If IsNull({BBJOBSHL.shipquant}) then

Use the formula for the sums



Hi mlmcc
Should I put it in the ShipQty subreport or in the ExtPrice sbreport. I tried both- but nothing.


Thanks mlmcc-
Although I wasn't understanding it at the time, your suggestion got me out of my rut and made me think in a different direction. Thanks for sending me on the right path.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial