Problem putting a SQL Statement into a form record source.

[PatT123]

This query works in the query window. If you name it in control source it
works. If you write the code into the control source of a form, it gives a
syntax error in the from clause..... Why? and can someone help me fix it?


SELECT TBLFUNDINGTOPOS.PosNo, TBLFUNDING.Funding, TBLFUNDING.PCA,
TBLPCA.Fund, TBLFUNDING.FedGrant_PH, TBLFUNDING.OthGrant_PH,
TBLFUNDINGTOPOS.Percent, TBLPCA.SubProgram, TBLFUNDINGTOPOS.TotalFTE,
CurrFTE.SumOfPercent AS CurrPurcent, TBLPCA.FundType
FROM (TBLFunds RIGHT JOIN TBLPCA ON TBLFunds.FUND = TBLPCA.Fund) RIGHT JOIN
(TBLFUNDING RIGHT JOIN (TBLFUNDINGTOPOS LEFT JOIN [SELECT
Sum(TBLFundingToPos.Percent) AS SumOfPercent, TBLFundingToPos.DIVFund
FROM TBLFundingToPos
GROUP BY TBLFundingToPos.DIVFund]. AS CurrFTE ON TBLFUNDINGTOPOS.DIVFund =
CurrFTE.DIVFund) ON TBLFUNDING.Funding = TBLFUNDINGTOPOS.Funding) ON
TBLPCA.PCA = TBLFUNDING.PCA;

Thank you

 

[Ron Weiner]

Pat

Looks to me like you need to replace the Square Brackets with parentheses
and loose the Dot in [SELECT Sum(TBLFundingToPos.Percent) AS SumOfPercent,
TBLFundingToPos.DIVFund FROM TBLFundingToPos GROUP BY
TBLFundingToPos.DIVFund]. AS CurrFTE

I think it should be (SELECT Sum(TBLFundingToPos.Percent) AS SumOfPercent,
TBLFundingToPos.DIVFund FROM TBLFundingToPos GROUP BY
TBLFundingToPos.DIVFund) AS CurrFTE

The rest of it looks OK assuming that all of the names in the sql statement
match real objects in the database.

 

[PatT123]

Ron - Thanks for helping. I did as you suggested. The Query will allow you
to view the design mode once. But after you close it and reopen it you get
Syntax error in from clause. I am not sure what is happening. It will run
with the query named fine as long as the query is called from the query
window. When you put the code into the record source for the form, it gives
the error as soon as you re bring up the form. Anyone know why?

Pat

Looks to me like you need to replace the Square Brackets with parentheses
and loose the Dot in [SELECT Sum(TBLFundingToPos.Percent) AS SumOfPercent,
TBLFundingToPos.DIVFund FROM TBLFundingToPos GROUP BY
TBLFundingToPos.DIVFund]. AS CurrFTE

I think it should be (SELECT Sum(TBLFundingToPos.Percent) AS SumOfPercent,
TBLFundingToPos.DIVFund FROM TBLFundingToPos GROUP BY
TBLFundingToPos.DIVFund) AS CurrFTE

The rest of it looks OK assuming that all of the names in the sql statement
match real objects in the database.
--
Ron W
www.WorksRite.com

This query works in the query window. If you name it in control source it
works. If you write the code into the control source of a form, it gives a
syntax error in the from clause..... Why? and can someone help me fix it?


SELECT TBLFUNDINGTOPOS.PosNo, TBLFUNDING.Funding, TBLFUNDING.PCA,
TBLPCA.Fund, TBLFUNDING.FedGrant_PH, TBLFUNDING.OthGrant_PH,
TBLFUNDINGTOPOS.Percent, TBLPCA.SubProgram, TBLFUNDINGTOPOS.TotalFTE,
CurrFTE.SumOfPercent AS CurrPurcent, TBLPCA.FundType
FROM (TBLFunds RIGHT JOIN TBLPCA ON TBLFunds.FUND = TBLPCA.Fund) RIGHT JOIN
(TBLFUNDING RIGHT JOIN (TBLFUNDINGTOPOS LEFT JOIN [SELECT
Sum(TBLFundingToPos.Percent) AS SumOfPercent, TBLFundingToPos.DIVFund
FROM TBLFundingToPos
GROUP BY TBLFundingToPos.DIVFund]. AS CurrFTE ON TBLFUNDINGTOPOS.DIVFund =
CurrFTE.DIVFund) ON TBLFUNDING.Funding = TBLFUNDINGTOPOS.Funding) ON
TBLPCA.PCA = TBLFUNDING.PCA;

Thank you