>I tried it and it failed in the Foxpro 9 form
>
>ldStartDate={}
>ldEndDate={}
>ldStartDate=thisform.startdate.value
>ldEndDate=thisform.enddate.value
>
> lcSQLCMD=[ SELECT arjobhd.custno, ];
> +[ arjobhd.customer, ];
> +[ ISNULL(ssslsman.salespn,'') as salespn, ];
> +[ arjobhd.transtype, ];
> +[ arjobtax.taxcode, ];
> +[ IsNull(arjobhd.invno,0) as invno, ];
> +[ arjob.ljob, ];
> +[ bbjthead.id, ];
> +[ ISNULL(SUM(arjob.price),0) as invoice, ];
> +[ ISNULL(SUM(arjob.frtamt),0) as freight, ];
> +[ CAST(00.00 AS decimal(10,2)) as tax, ];
> +[ CAST(00.00 AS decimal(10,2)) as cost_lab, ];
> +[ CAST(00.00 AS decimal(10,2)) as cost_mat, ];
> +[ CAST(00.00 AS decimal(10,2)) as cost_fin, ];
> +[ CAST(00.00 AS decimal(10,2)) as cost_pur, ];
> +[ CAST(00.00 AS decimal(10,2)) as Totcost, ];
> +[ CAST(00.00 AS decimal(10,2)) as TotInv, ];
> +[ CAST(00.00 AS decimal(10,2)) as profper ];
> +[ FROM arjobhd ];
> +[ JOIN arjob on arjobhd.invno=arjob.invno ];
> +[ JOIN arjobtax on arjobhd.invno=arjobtax.invno ];
> +[ JOIN bbjthead on bbjthead.ljob=arjob.ljob ];
> +[ JOIN ssslsman on arjobhd.salespnno=ssslsman.salespnno ];
> +[ WHERE ];
> +[ ( bbjthead.closedate between ?ldstartdate and ?ldenddate ];
> +[ AND arjobhd.invdate IS NOT NULL ];
> +[ AND bbjthead.closedate >= arjobhd.invdate ) ];
> +[ OR ];
> +[ ( arjobhd.invdate between ?ldstartdate and ?ldenddate ];
> +[ AND bbjthead.closedate IS NOT NULL ];
> +[ AND invdate >= bbjthead.closedate ) ];
> +[ Group by arjobhd.custno, ];
> +[ arjobhd.customer, ];
> +[ ssslsman.salespn, ];
> +[ arjobhd.transtype, ];
> +[ arjobtax.taxcode, ];
> +[ arjobhd.invno, ];
> +[ arjob.ljob, ];
> +[ bbjthead.id ];
> +[ ORDER by arjob.ljob, arjobhd.invno ]
>
>
> SQLEXEC(gnConnect,lcSQLCMD,'jobcost')
>
>SELECT jobcost
>brow
What error did you get? Also, don't write this code like this. Copy the select statement from SQL Server and try
text to lsSQLCmd noshow
select ..
...
... where DateFld >=?m.ldStartDate and DateFld < ?m.ldEndDate
ORDER BY ...
endtext
If it's not broken, fix it until it is.
My Blog