im running a report based on a query. one of the fields in the query is
an expression that uses a subquery and states IIf([campaign] IN (select [campaign] fromtblDishCampaign),1,0).....this works fine in the query, but when i try to run this in the report i get the "Multi-level GROUP BY clause is notallowed in subquery" error. i switched to using the DLookUp this way....IIf([campaign] = DLookup"([campaign]","[tblDish]"),1,0)..... this runs in both the query, and the report, but only returns a 1 or True if the value of [campaign] is the first value in the [tblDish] table is there
anyway i can get this to lookup all of the values in the table, so that i dont have to "hard code" them in the query, and then change them every time a campaign code changes?