Access Bug for comlex queries?

[Michelle]

I am developing an app in Access 2000. I have a series of
queries that work just fine but if I try to change them,
at all, I get the error: SYNTAX ERROR IN FROM CLAUSE.
I mean even if I put in a space and then remove it or if I
copy the text to Notepad and then paste it right back.
Is this some known bug? What can I do about it? I really
need to make a slight alteration to the SQL.

Example:
SELECT P2.[PERIOD], (P2.[MONTH] & " " & P2.[YEAR]) AS
PeriodTxt, a.OnTime, SUM(ABS(PD2.[RQMTS_ACT_BUYOFF_DATE]
IS NOT NULL)) AS Complete, ((a.OnTime) / (IIF(Complete =
0, 1, Complete)) * 100) AS PercentCompleteOnTime
FROM (PROJECT_DATA AS PD2 RIGHT JOIN PERIODS AS P2 ON
(DatePart("m", P2.[END_OF_PERIOD_DATE])=DatePart("m",PD2.
[RQMTS_ACT_BUYOFF_DATE])) AND (DatePart("yyyy", P2.
[END_OF_PERIOD_DATE])=DatePart("yyyy",PD2.
[RQMTS_ACT_BUYOFF_DATE]))) INNER JOIN [SELECT P1.[PERIOD],
( P1.[MONTH] & " " & P1.[YEAR]) AS
PeriodTxt
, NZ(SUM(ABS((PD1.[RQMTS_SCHD_BUYOFF_DATE] -
PD1.[RQMTS_ACT_BUYOFF_DATE])>= 0)),0) As OnTime
FROM PROJECT_DATA As PD1
right JOIN PERIODS As P1
ON (DatePart("yyyy", P1.[END_OF_PERIOD_DATE])=DatePart
("yyyy",PD1.[RQMTS_ACT_BUYOFF_DATE]))
AND (DatePart("m", P1.[END_OF_PERIOD_DATE])
=DatePart("m",PD1.[RQMTS_ACT_BUYOFF_DATE]))
GROUP BY P1.[PERIOD], P1.[MONTH], P1.[YEAR]
]. AS a ON a.PERIOD = P2.PERIOD
WHERE P2.[PERIOD] <> 0
GROUP BY P2.[PERIOD], P2.[MONTH], P2.[YEAR], a.OnTime;


Thank you for any input,
Michelle

 

[Allen Browne]

Quite honestly, Michelle, I'm surprised that JET can make sense of the FROM
clause at all. It does look like quite a messy non-equi join, with many
function calls. There may be more efficient ways to write this?

 

[CSmith]

Hi,

I'm wondering if you're reaching a character or buffer limit. Try using
shorter alias for everything including PercentCompleteOnTime, if it's not
too late.