aggregate functions are only allowed on output fields of Record Source

[mague4718]

I am using Access Project with a SQL backend. I am running a report and in a the control source of a text box I am trying to calculate: =Sum(IIf([ethnicity]='X',1,0))


My Report Record Source is:
SELECT dbo.AppPos.AppPos, dbo.AppPos.Status, dbo.AppPos.APAppID, dbo.AppPos.APPosID, dbo.Applicants.Ethnicity AS Ethnicity, dbo.Applicants.Gender,
dbo.Positions.fldPosName, dbo.Ethnicity.fldEthDesc, dbo.AppPos.Disabled, dbo.AppPos.VietnamEraVeteran, dbo.AppPos.Accomodations,
dbo.Status.Description, dbo.Positions.fldPosMemo, dbo.AppPos.Source
FROM dbo.Status INNER JOIN
dbo.Ethnicity INNER JOIN
dbo.Applicants INNER JOIN
dbo.Positions INNER JOIN
dbo.AppPos ON dbo.Positions.fldPosNo = dbo.AppPos.APPosID ON dbo.Applicants.ApplicantsID = dbo.AppPos.APAppID ON
dbo.Ethnicity.fldEthCode = dbo.Applicants.Ethnicity ON dbo.Status.Status = dbo.AppPos.Status


But I keep getting the following Error
aggregate functions are only allowed on output fields of a Record Source

This worked [=Sum(IIf([ethnicity]='X',1,0))} before I converted the Access DB into an Access Project and moved the data into SQL.

Please help!!!