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

wgraphics
wgraphics used Ask the Experts™
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
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}

@Quan_UnitPrice:
{BBJTHEAD.QUAN}*{@Unit Price}

@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
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

Author

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

Author

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

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