Zahid,

I think you are correct. I examined the stuctures of the open view, and the field was 12 "character" numberic. The NULL had to be the proble.

Thanks,


>Steve -
>If you're right about that line being the culprit, try:

IIF(ISNULL(DCMIINVENTORY.iQtyOnHand),;
>    0000000000,;
>    IIF((DCMIINVENTORY.iQtyOnHand - DCMIINVENTORY.iQtyCommitted)>= 0,;
>        (DCMIINVENTORY.iQtyOnHand - DCMIINVENTORY.iQtyCommitted),;
>        0000000000)) AS "iQtyAvailable" ,;

That way, you determine if any inventory items were found (via the ISNULL() function) before performing the calculation. When no items are returned from the LEFT JOINed table, .NULL. is returned.
>
>Hope this helps!
>zahid
>
>
>>I have a view where this error occurs occsionally. I will post the view definition below, but I think the error is comming out of one field expresion which is:
>>
>>IIF((DCMIINVENTORY.iQtyOnHand - DCMIINVENTORY.iQtyCommitted)>= 0,(DCMIINVENTORY.iQtyOnHand - DCMIINVENTORY.iQtyCommitted),0000000000) AS "iQtyAvailable" ,;
>>
>>the error was generared this morning, while testing the APP at the clients site, where the inventory table did not contain a record for this item, which I suspect caused the error.
>>
>>The entire view SQL definiton is:
>>
>>SELECT ;
>> DCMIRELEASES.CID , ;
>> DCMIRELEASES.cArtist_fk , ;
>> DCMIRELEASES.cInventoryCode_fk , ;
>> DCMIRELEASES.cItemCode_fk ,;
>> DCMIRELEASES.cLabel_fk , ;
>> DCMIRELEASES.cVendorCode_fk , ;
>> DCMIARTIST.cArtist , ;
>> DCMIRELEASES.cCatNo ,;
>> DCMIINVTYCODE.cCodeNo , ;
>> DCMIRELEASES.cInputBy , ;
>> DCMILABELS.cLabelName , ;
>> DCMIINVTYCODE.cDescription AS "cInvtyCodeDescription" ,;
>> DCMIITEMCODE.cItemCode , ;
>> DCMIITEMCODE.cDescription AS "cItemDescription" , ;
>> DCMIVENDORS.cName ,;
>> DCMIRELEASES.cND , ;
>> DCMIRELEASES.cTitle , ;
>> DCMIRELEASES.cUPC , ;
>> DCMIVENDORS.cVendorCode , ;
>> DCMIRELEASES.dDue ,;
>> DCMIRELEASES.dInput , ;
>> DCMIRELEASES.dStreet , ;
>> DCMIRELEASES.iExtQty , ;
>> IIF((DCMIINVENTORY.iQtyOnHand - DCMIINVENTORY.iQtyCommitted)>= 0,(DCMIINVENTORY.iQtyOnHand - DCMIINVENTORY.iQtyCommitted),0000000000) AS "iQtyAvailable" ,;
>> DCMIRELEASES.lCutOut , ;
>> DCMIRELEASES.lPending , ;
>> DCMIRELEASES.nListPrice , ;
>> DCMIRELEASES.nCMIPrice ,;
>> DCMIRELEASES.nCustPrice , ;
>> DCMIRELEASES.mRemarks , ;
>> DCMIARTIST.lArtistDummy , ;
>> DCMIINVTYCODE.lInvtDummy ,;
>> DCMIITEMCODE.lItemsDummy , ;
>> DCMILABELS.lLabelsDummy , ;
>> DCMIRELEASES.lReleasesDummy , ;
>> DCMIVENDORS.lVendorsDummy ,;
>> DCMIINVENTORY.iQtyCommitted ;
>>FROM ( ( ( ( ( CMI!DCMIRELEASES ;
>> INNER JOIN CMI!DCMIARTIST ON DCMIRELEASES.cArtist_fk = DCMIARTIST.CID ) ;
>> INNER JOIN CMI!DCMIINVTYCODE ON DCMIRELEASES.cInventoryCode_fk = DCMIINVTYCODE.CID ) ;
>> INNER JOIN CMI!DCMILABELS ON DCMIRELEASES.cLabel_fk = DCMILABELS.CID ) ;
>> INNER JOIN CMI!DCMIITEMCODE ON DCMIRELEASES.cItemCode_fk = DCMIITEMCODE.CID ) ;
>> INNER JOIN CMI!DCMIVENDORS ON DCMIRELEASES.cVendorCode_fk = DCMIVENDORS.CID ) ;
>> LEFT OUTER JOIN CMI!DCMIINVENTORY ON DCMIRELEASES.CID = DCMIINVENTORY.cReleases_fk ;
>>GROUP BY DCMIRELEASES.CID
>>
>>Can someone suggest a way to accomplish what I am trying o do here safely?
>>
>>Thanks,