> Steve
>
> Thats great. Actually I have no idea why between is not working.
>
> Regards
> Tushar
> >
> >
> >
> > Hi Tushar,
> >
> > Problem solved, finally, and thanks to you! I've used your framework of the code, but however,it took me many attempts before I realised that the Between was not working. I've reworked the code slightly, using the best of all the code you've sent me. I'm sure you're also relieved, to have my problem solved. I really appreciate the help you've given me.
> >
> > With best regards,
> >
> > BTW, why do think the 'Between' was not working?
> >
> > Steve
> >
> > My code:
> >
> >

SELECT A.ddate,A.cblock,c.cName,;
> > ROUND(iif(not isnull(c.nspx) and not isnull(c.nspy) and C.nspx<>0 and C.nspy<>0,C.narea*(43560/(C.nspx*C.nspy)),C.nrdmPlants),2) as Plants,; 
> > (SELECT sum(RowPlants(E.nspx,E.nspy,E.narea,E.nrdmPlants)) as TotPlants FROM Blockprofile E ;
> > WHERE E.cParent=A.cBlock AND (A.ddate>=E.dstart AND A.ddate <=E.dend) );
> > From Dailywork2 A;
> > Left Outer JOIN BlockProfile C;
> > on A.cBlock = C.cParent;
> > and (A.ddate>=C.dstart AND A.ddate <=C.dend)
> > 
> > FUNCTION RowPlants
> > LPARAMETERS lnX,lnY,lnArea,lnNrdm
> > IF ISNULL(lnX) OR ISNULL(lnY) OR lnX=0 OR lnY=0
> > 	RETURN lnNrdm
> > ELSE
> > 	RETURN ROUND(lnArea*43560/(lnX*lnY),2)
> > ENDIF

Thanks, Tushar,

This was one of my toughest queries partly because of the challenge of putting together a mixed bag of data, and also surprisingly enhanced by the fact that some things like 'between' decided not to work!

Just thought I'll attach the route where I am heading with what I have got with your help.

** Advancement- Trying to incorporate sub blocks also...(mixed block & subblock data entry) 
SELECT A.ddate Date,;
A.cblock Block,;
B.nArea Area,;
C.cName SubBlock,;
ROUND(iif(not isnull(c.nspx) and not isnull(c.nspy) and C.nspx<>0 and C.nspy<>0,C.narea*(43560/(C.nspx*C.nspy)),C.nrdmPlants),2) as Plants,; 
IIF(C.cparent=A.Cblock,(SELECT sum(RowPlants(C.nspx,C.nspy,C.narea,C.nrdmPlants)) as TotPlants FROM Blockprofile C ;
WHERE C.cParent=A.cBlock AND (A.ddate>=C.dstart AND A.ddate <=C.dend)),;
ROUND(iif(not isnull(c.nspx) and not isnull(c.nspy) and C.nspx<>0 and C.nspy<>0,C.narea*(43560/(C.nspx*C.nspy)),C.nrdmPlants),2))as TotPlants,;
(A.nMen+A.nWmn+A.nadl) as Heads,;
ROUND((A.nMen+A.nWmn+A.nadl)/;
((SELECT sum(RowPlants(C.nspx,C.nspy,C.narea,C.nrdmPlants)) as TotPlants FROM Blockprofile C ;
WHERE C.cParent=A.cBlock AND (A.ddate>=C.dstart AND A.ddate <=C.dend)))*(C.narea*(43560/(C.nspx*C.nspy))),2) as HdsSpread;
From Dailywork2 A;
LEFT Outer JOIN BlockProfile C;
ON(A.cBlock = C.cParent OR A.cBlock=C.cName) and (A.ddate>=C.dstart AND A.ddate <=C.dend);
LEFT OUTER JOIN Block2 B;
on A.cBlock=B.block


FUNCTION RowPlants
LPARAMETERS lnX,lnY,lnArea,lnNrdm
IF ISNULL(lnX) OR ISNULL(lnY) OR lnX=0 OR lnY=0
	RETURN lnNrdm
ELSE
	RETURN ROUND(lnArea*43560/(lnX*lnY),2)
ENDIF

Thanks, again,

Regards,

Steve.