**Solved**Query Problem
Hi All,
Okay, so I had this query working quite fine a little while ago, I added in another field with some expressions and started getting this warning/error:
This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.
So I deleted trimmed down the new field added in, still same error, so I removed it, and still same error. Obviously along the way of doing this I've changed something somewhere but cant for the life of me work out what it is!
Here's my SQL:
I dont know if anyone has a proofing program that they would kindly run this through to ensure that all syntax is correct? Ive gone through it and cant find the mistake.
Would be much appreciated.
Thanks
Hi All,
Okay, so I had this query working quite fine a little while ago, I added in another field with some expressions and started getting this warning/error:
This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.
So I deleted trimmed down the new field added in, still same error, so I removed it, and still same error. Obviously along the way of doing this I've changed something somewhere but cant for the life of me work out what it is!
Here's my SQL:
Code:
SELECT "" AS Blank000, "" AS Blank001, "" AS Blank002, "" AS Blank003, "" AS Blank004, "" AS Blank005, "" AS Blank006, "" AS Blank007, "" AS Blank008, "" AS Blank009, "" AS Blank010, "" AS Blank011, "" AS Blank012, "" AS Blank013, "" AS Blank014, "" AS Blank015, IIf([tblAccess].[Description]="Easy Accessibility","1","0") AS EasyAccessibility, IIf([tblAccess].[Description]="Medium Accessibility","1","0") AS MediumAccessibility, IIf([Material Score]=0,"NADIS",tblCondition.Description) AS ExtentOfDamage, IIf([tblAccess].[Description]="Difficult Accessibility","1","0") AS DifficultAccessibility, Buildings.[Building Name], Milestones.Notes, IIf([Material Score]=0,"NADIS",[tblProductTypes].[Description]) AS ProdTypeDescr, [Survey Items].ID, [Survey Items].[Survey ID], "" AS Blank101, "" AS Blank102, "" AS Blank103, "" AS Blank104, "" AS Blank105, [Survey Items].[Sample Number], "" AS Blank107, "" AS Blank108, "" AS Blank109, "" AS Blank110, "" AS Blank111, "" AS Blank112, "" AS Blank113, "" AS Blank114, "" AS Blank115, "" AS Blank116, "" AS Blank117, "" AS Blank118, "" AS Blank119, "" AS Blank120, "" AS Blank121, "" AS Blank122, First([Survey Items].PPCNotes) AS [Material Comments], [Survey Items].[Recommended Action], [Survey Items].Floor, "" AS Blank201, [Survey Items].Identification, IIf([Material Score]=0," ",[tblLocation].[Description]) AS LcationDesc, "" AS Blank301, "" AS Blank302, "" AS Blank303, "" AS Blank304, [Survey Items]![ID] & ".jpg" AS Photo, "" AS Blank401, Projects.[Site Address], "" AS Blank501, "" AS Blank502, "" AS Blank503, "" AS Blank504, "" AS Blank505
FROM ((((tblMaintenanceTypes RIGHT JOIN ((((((((((((((Surveys RIGHT JOIN Projects ON Surveys.[Project Number] = Projects.[Project Number]) LEFT JOIN [Survey Items] ON Surveys.ID = [Survey Items].[Survey ID]) LEFT JOIN Buildings ON [Survey Items].[Building ID] = Buildings.ID) LEFT JOIN tblProductTypes ON [Survey Items].[Product Type] = tblProductTypes.ProductType) LEFT JOIN tblCondition ON [Survey Items].Condition = tblCondition.Score) LEFT JOIN tblSurfaceTreatments ON [Survey Items].[Surface Treatment] = tblSurfaceTreatments.SurfaceTreatment) LEFT JOIN tblAsbestosType ON [Survey Items].[Asbestos Type] = tblAsbestosType.AsbestosType) LEFT JOIN tblOccupancy ON [Survey Items].[Normal Occupant Activity] = tblOccupancy.Score) LEFT JOIN tblLocation ON [Survey Items].[PPCPA-Location] = tblLocation.Score) LEFT JOIN tblAccess ON [Survey Items].[PPCPA-Accessibility] = tblAccess.Score) LEFT JOIN tblAmount ON [Survey Items].[PPCPA-Amount] = tblAmount.Score) LEFT JOIN tblNoOfPeople ON [Survey Items].[PPCPA-NoOfPeople] = tblNoOfPeople.Score) LEFT JOIN tblUse ON [Survey Items].[PPCPA-Use] = tblUse.Score) LEFT JOIN tblAverageTime ON [Survey Items].[PPCPA-AverageTime] = tblAverageTime.Score) ON tblMaintenanceTypes.Score = [Survey Items].[PPCPA-MaintenanceType]) LEFT JOIN tblFrequency ON [Survey Items].[PPCPA-Frequency] = tblFrequency.Score) LEFT JOIN Clients ON Projects.[Client ID] = Clients.ID) LEFT JOIN Staff ON [Survey Items].[Surveyor ID] = Staff.[Staff ID]) LEFT JOIN Milestones ON Surveys.[Project Number] = Milestones.[Project Number]
GROUP BY "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", IIf([tblAccess].[Description]="Easy Accessibility","1","0"), IIf([tblAccess].[Description]="Medium Accessibility","1","0"), IIf([Material Score]=0,"NADIS",tblCondition.Description), IIf([tblAccess].[Description]="Difficult Accessibility","1","0"), Buildings.[Building Name], Milestones.Notes, IIf([Material Score]=0,"NADIS",[tblProductTypes].[Description]), [Survey Items].ID, [Survey Items].[Survey ID], "", "", "", "", "", [Survey Items].[Sample Number], "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", [Survey Items].[Recommended Action], [Survey Items].Floor, "", [Survey Items].Identification, IIf([Material Score]=0," ",[tblLocation].[Description]), "", "", "", "", [Survey Items]![ID] & ".jpg", "", Projects.[Site Address], "", "", "", "", "", Milestones.Position
HAVING ((([Survey Items].[Survey ID])=[What Project?]) AND ((Milestones.Position)=8));I dont know if anyone has a proofing program that they would kindly run this through to ensure that all syntax is correct? Ive gone through it and cant find the mistake.
Would be much appreciated.
Thanks
Last edited:
