VB6 CALC Aggregation on ADO Shape when returns Null getting General Error in Data Report

Question

I am using ado shape command on my data report, it works fine but when my aggregate function CALC(agrProfit/agrExtended*100) is null or 0/0*100 it shows general error and data report not showing up. Please Help.

mRS.Open "SHAPE {select products.productid,products.productcode,isnull(products.description,descr) as description,isnull(vendor.description,'*** NOT FOUND ***') as groupdescription, " & _
    "isnull(sum(totalcost),0) as mTotalCost,isnull(sum(extended) - (sum(totalcost)),0) as mProfit,  " & _
    "sum(charges) as mCharges,sum(discount) as mDiscounts, sum(retextended) as mReturns, " & _
    "reportuom, sum(totalcost) as mTotalCost, isnull(case when sum(extended) = 0 then 0 else (sum(extended) - (sum(totalcost)))/sum(extended)*100 end,0)  as mgpm, sum(totalcost) as mTotalCost, case when sum(extended) = 0 then 0 else (sum(extended) - (sum(totalcost)))/sum(extended)*100 end  as mgpm, sum(case when extended < 0 then  (0 - (totalqty/products.reportqty))  else (totalqty/products.reportqty) end) as mTotalQty, isnull(sum(extended),0) as mExtended,  sum(case when extended < 0 then  (0 - (totalqty/products.reportqty)) else (totalqty/products.reportqty) end) / " & mTotalQty & " * 100 as mPercTotalQty, sum(extended) / " & mTotalExtended & " * 100 as mPercExtended " & _
    "From " & _
        "(select finishedsales.QtyReturned,finishedsales.productid,finishedsales.description as descr, finishedsales.averageunitcost* case when [return]=1 then convert(money,0-totalqty) else totalqty end as TotalCost,(chargeallowance * qty) + (chargeamountdiscounted * qty) as charges,(allowance * qty) + (amountdiscounted * qty)+ (extended-(extended * multiplier)) as discount,0 as rettotalqty, 0 as retextended,totalqty,round(extended * multiplier,4) as extended  From finishedsales " & _
        " left join products on products.productid = finishedsales.productid " & _
        .gReportCriteria & _
        "Union All " & _
        "select finishedsales.QtyReturned, finishedsales.productid,finishedsales.description as descr,0 as totalcost,0 as charges,0 as discount,totalqty as rettotalqty ,abs(round(extended,4)) as retextended,0 as totalqty, 0 as extended From finishedsales " & _
            "left join products on products.productid = finishedsales.productid " & _
        Replace(UCase(.gReportCriteria & " and [RETURN] = 1"), "[RETURN] = 0", "[return] = 1") & _
    ") as finishedsales " & _
    "left join products on products.productid=finishedsales.productid  " & _
    "left join vendor on products.vendorcode=vendor.vendorcode " & _
    "group by descr,products.productid,products.productcode,products.description,vendor.description,reportuom " & _
    "order by groupdescription, " & IIf(frmReportProducts.chkTop And fVal(frmReportProducts.txtTop) > 0, "finishedsales.mtotalqty desc,", "") & " products.description}  AS Command1 COMPUTE Command1, SUM(Command1.mTotalQty) AS agrTotalQty,  SUM(Command1.mExtended) AS agrExtended, SUM(Command1.mProfit) AS agrProfit, CALC(agrProfit/agrExtended*100) As agrGPM BY groupdescription", mcn

score 2 · Accepted Answer · answered Mar 02 '16 at 00:02

So it looks like you're using the ADO Data Shaping functions here, and the CALC(expression) allows you to use VBA functions listed here within the expression. @C-Pound Guru's suggestion causes an error since NULLIF() is not a VBA function, but the whole expression can be rewritten like this:

CALC(IIF(IsNull(agrProfit), 0, IIF(agrProfit=0, 0, agrProfit/agrExtended) *100))

Let me know if this takes care of your issue.

score 1 · Answer 2 · answered Feb 22 '16 at 14:23

1

If your SQL Server is 2005 or newer you can use NULLIF in conjunction with ISNULL:

Replace agrProfit/agrExtended with

ISNULL(agrProfit / NULLIF(agrExtended,0),0)

This will return zero when agrExtended=0 rather than causing a divide by zero error.

answered Feb 22 '16 at 14:23

C-Pound Guru

15,967
6
46
67

CALC(ISNULL(agrProfit/NULLIF(agrExtended*100,0),0)) it says Column NULLIF was used in a CALC expression but is not defined in the rowset – FatalError Feb 23 '16 at 01:27
Calc isn't a built in SQL function (as far as I know). Perhaps you can show that function and we can see if the divide by zero can be handled there... – C-Pound Guru Feb 23 '16 at 18:09

score 0 · Answer 3 · answered Mar 01 '16 at 19:09

It seems that you're using MS Access or something that interfaces with MS Access. If that is the case, maybe you can use Switch:

Replace:

CALC(agrProfit / agrExtended * 100)

With:

Switch(
  ISNULL(SUM(Command1.mExtended)), 0,
  ISNULL(SUM(Command1.mProfit)), 0,
  IIF(SUM(Command1.mExtended) = 0, 0, SUM(Command1.mProfit) / SUM(Command1.mExtended) * 100)
      )

The idea is to replace NULL with 0, replace Divide by 0 with 0, or else return the actual ratio.

VB6 CALC Aggregation on ADO Shape when returns Null getting General Error in Data Report

3 Answers3