Query Problem - Query works in Query window but not as code in a f

[PatT123]

This Query will work as QUERY6 in the form and in the query window, but when
I take the code and put it into the record source of a form I get a syntax
error in from clause. I tried the sub query with and without (). Same
result. Can someone help?


SELECT TBLFUNDINGTOPOS.PosNo, TBLFUNDING.Funding, TBLFUNDING.PCA,
TBLPCA.Fund, TBLFUNDING.FedGrant_PH, TBLFUNDING.OthGrant_PH,
TBLFUNDINGTOPOS.Percent, TBLPCA.SubProgram, TBLFUNDINGTOPOS.TotalFTE,
CurrFTE.SumOfPercent
FROM (TBLIndex RIGHT JOIN TBLPosition ON TBLIndex.Index = TBLPosition.Index)
RIGHT JOIN ((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) ON TBLPosition.PosNo = TBLFUNDINGTOPOS.PosNo;

 

[Van T. Dinh]

The " []. " notation for SubQuery / SubSQL always gets me as well. I am
reasonable with SQL but I have never been able to use this notation.

Luckily, the parentheses seem to work for me. Try replacing " [...]. " with
just "( ...)" without the dot after.

HTH
Van T. Dinh
MVP (Access)

 

[PatT123]

Sorry I am new to discussion groups. I did try changing the [] with () and
removing the . however Access seems to be changing the code when I save the
query. Even with the () it changes fields to brackets and will not run as a
code in the record source of the form. The only thing I can think to do at
this time is to put the query into a module and update the record source on
open.

Thanks again.

The " []. " notation for SubQuery / SubSQL always gets me as well. I am
reasonable with SQL but I have never been able to use this notation.

Luckily, the parentheses seem to work for me. Try replacing " [...]. " with
just "( ...)" without the dot after.

HTH
Van T. Dinh
MVP (Access)

This Query will work as QUERY6 in the form and in the query window, but when
I take the code and put it into the record source of a form I get a syntax
error in from clause. I tried the sub query with and without (). Same
result. Can someone help?


SELECT TBLFUNDINGTOPOS.PosNo, TBLFUNDING.Funding, TBLFUNDING.PCA,
TBLPCA.Fund, TBLFUNDING.FedGrant_PH, TBLFUNDING.OthGrant_PH,
TBLFUNDINGTOPOS.Percent, TBLPCA.SubProgram, TBLFUNDINGTOPOS.TotalFTE,
CurrFTE.SumOfPercent
FROM (TBLIndex RIGHT JOIN TBLPosition ON TBLIndex.Index = TBLPosition.Index)
RIGHT JOIN ((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) ON TBLPosition.PosNo = TBLFUNDINGTOPOS.PosNo;