What's wrong with this access query? Query Builder doesn't like it?
I am configuring a Table Adapter using following access query. This is an exact copy of the query that is saved on the access database. Now the Query Builder is complaining about
Error in list of function arguments: 'IS' not recognized.
Error in list of function arguments: '=' not recognized.
Unable to parse query text.
and
Undefined function '[Year]' in expression
what am i doing wrong? here's the query
SELECT
CD.CompanyCode,
CF.CompanyKey,
CF.PeriodEndDate,
IIF(CF.AnalystDate IS NULL, DATESERIAL(YEAR(CF.PeriodEndDate),MONTH(CF.PeriodEndDate)+2,DAY(CF.PeriodEndDate)), CF.AnalystDate) AS [Analysis Date],
CF.MonthEndClose * CF.OrdinaryShares AS [Market Cap],
IIF(CF.FiguresType = 'A',1,0) AS [Type of results],
CF.Risk AS [Health Score],
CF.RiskRating AS [Health Rating],
CF.ROA,
CF.ROE,
SI.IndustryCode AS [Industry Group Code],
I.IndustryName AS [Industry Group Desc]
FROM ((CompanyDetails AS CD INNER JOIN CompanyFigures AS CF ON CD.CompanyKey = CF.CompanyKey)
LEFT JOIN tblSubIndustry AS SI ON CF.SubIndustryCode = SI.SubIndustryCode)
LEFT JOIN tblIndustry AS I ON SI.IndustryCode = I.IndustryCode
WHERE CF.PeriodEndDate Between #01-JUL-1996# And #30-JUN-2005#
AND DATEDIFF('m',CF.PeriodEndDate, IIF(CF.AnalystDate IS NULL, DATESERIAL(YEAR(CF.PeriodEndDate),MONTH(CF.PeriodEndDate)+2,DAY(CF.PeriodEndDate)), CF.AnalystDate)) <= 4
ORDER BY CF.CompanyKey, CF.PeriodEndDate
