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

on
Hello…
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

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}

@Quan_UnitPrice:

@Sum_Unit:
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!
Megan
Comment
Watch Question

Do more with

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

Use the formula for the sums

mlmcc

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

Commented:
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.
Meg

Do more with