I have a query set up with IIF functions on currency fields. Example:
Denied Amount: IIf([CLAIM STATUS]=”denied” Or Left([CLAIM STATUS],3)=”rej”,[ADJUSTED AMOUNT],””)
If the status in the CLAIM STATUS field is equal to denied or rej, then I want the dollars in the ADJUSTED AMOUNT field to be present AS Denied Amount. This works great, until I build the next query and try to sum. I get the error ‘The Microsoft database engine could not execute the SQL statement because it contains a field that has an invalid data type”. Well, I made sure and set the properties of the alias field ‘Denied Amount’ to currency, and it still won’t work. Does the IIF function convert everything to text, so that I will never be able to do any further calculations on the data? Anyway around this?