>I had a little trouble with the TAB button trying to modify the layout of the select stament. But here it is
>
>SELECT cArtCode, UPPER(cArtGrp) AS cArtgrp, cArtNaam, ;
> SUM(iAantal*IIF(factuur.lCredNota,-1,1))AS iTotAant,;
> SUM(iAantal*nArtInkPrs*IIF(Factuur.lcrednota,-1,1)) As nTotInk, ;
> SUM(iAantal*nArtPrs*IIF(factuur.lCredNota,-1,1)) AS nTotVerk,;
> SUM(iAantal*(nartPrs*IIF(facregels.iArttype=1,factuur.nKortPerc1/100,;
> factuur.nKortperc2/100) ) )as nKort ;
>FROM FacRegels,Factuur ;
>WHERE Between(Factuur.dFacDatum,dDatVan,dDatTot) ;
>AND YEAR(facregels.dFacdatum)+FacRegels.iFactuurnr = ;
> YEAR(Factuur.dFacdatum)+ Factuur.iFactuurnr ;
>UNION ;
>SELECT cArtCode, UPPER(cArtGrp) AS cArtGrp, cArtNaam, ;
> SUM(iAantal*IIF(fac.lCredNota,-1,1)) AS iTotAant, ;
> SUM(iAantal*nArtInkPrs*IIF(Fac.lCrednota,-1,1)) As nTotInk, ;
> SUM(iAantal*nArtPrs*IIF(fac.lCredNota,-1,1)) AS nTotVerk, ;
> SUM(iAantal*(nartPrs*IIF(facrg.iArttype=1,fac.nKortPerc1/100, ;
> fac.nKortperc2/100) ) )as nKort ;
>FROM FacRg,Fac ;
>WHERE Between(Fac.dFacDatum,dDatVan,dDatTot) ;
>AND YEAR(facrg.dFacdatum) + Facrg.iFactuurnr = ;
> YEAR(Fac.dFacdatum) + Fac.iFactuurnr ;
>GROUP BY 1 ;
>ORDER BY 2,1 ;
>INTO CURSOR crVerk
>The problem i have is when there's no record found in the Fac or Factuur tables it returns only one record whith an iAantal (amount) which is incorrect.
>
>Does anybody know this fenomenon or can someone do me an better suggestion for this
>

I don't know the reason the amount is incorrect, but in order to return more than one record, you need to use an OUTER JOIN between the two tables each SELECT substatement, so that records with no matching Fac or Factuur are included with nulls for values (use NVL() if the occurance of NULLS in the result are a problem.) The default behavior of a JOIN implemented as an element of a WHERE without resulting to multiple UNIONs is equivalent to an INNER JOIN, which only includes records for which there is a common match condition in both tables.

If it's possible that more than one record of a given value can occur, you need to use UNION ALL rather than just UNION; UNION by itself only presents one distinct record occurance even if it occurs multiple times in one or both parts of a UNION.