Syntax error in FROM clause? 1

[simon551]

PHV helped me write this Sql code, and it works great, but I am unable to reuse it. I changed the name of the form that it will run off and then I get a 'Syntax Error in FROM clause' error message. I've double-checked over and over that I didn't change anything in the SQL besides the form items (txtEnd and frmReportOne) but I get the same message. It is something to do with the SaleDate<Forms!... part, because that is the only part I have to access through the SQL.It's not visible in Design View. I don't understand the Sql a whole lot. PHV if your out there today I'd love to here from you. Or anyone who has thoughts on this strange-seeming (to me) problem.
thanks.



SELECT I.LotID, I.SecID, I.MgrID, M.Manager, I.Description, I.Tckr, I.Pdate, P.Date, P.Date-I.Pdate AS PeriodHeld, IIf(P.Date-I.Pdate<=365,"yes","no") AS Shortterm, I.NumShares-Nz(S.TotalSold,0) AS BalShares, P.Price, Nz([BalShares],0)*P.Price AS FMVRD, I.NumShares, I.Price1, Nz([BalShares],0)*I.Price1 AS Cost1, I.Price2, Nz([BalShares],0)*I.Price2 AS Cost2, [FMVRD]-[Cost1] AS Unrealized1, [FMVRD]-[Cost2] AS Unrealized2
FROM ((tblInventory AS I INNER JOIN tblPrices AS P ON I.SecID = P.SecID) INNER JOIN tblManagers AS M ON I.MgrID = M.MgrID) LEFT JOIN [SELECT LotID, Sum(NumSold) AS TotalSold FROM tblSales

WHERE SaleDate < [Forms]![frmReportOne]![txtEnd] GROUP BY LotID]. AS S ON I.LotID = S.LotID

WHERE I.MgrID = [Forms]![frmReportOne]![txtMgrID]
AND I.Pdate < [Forms]![frmReportOne]![txtEnd]
AND P.Date = [Forms]![ReportOne]![txtEnd]
AND I.NumShares > Nz(S.TotalSold,0)
ORDER BY I.Description;

 

[PHV]

Replace this:
LEFT JOIN [SELECT
By this:
LEFT JOIN (SELECT
And this:
GROUP BY LotID]. AS S
By this:
GROUP BY LotID) AS S

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886