I'm creating an honor roll for my grades table. When I try to run an average on the output from this statement it gives me a data type mismatch. I'm assuming that the output is seen as text even though I have formatted it in the query as a number. Is there any way to make sure this is output as a number. The statement is: GradeNum: IIf([Grade]="A+",99,IIf([Grade]="A",95,IIf([Grade]="A-",91,IIf([Grade]="B+",88,IIf([Grade]="B",85,IIf([Grade]="B-",82,IIf([Grade]="C+",78,IIf([Grade]="C",75,IIf([Grade]="C-",72,IIf([Grade]="D",65,IIf([Grade]="F",0,"?"))))))))))) Thank you.

I set up a small table and used your iif. I also did an AVG using the Totals icon on query design screen. I got a numeric result without error. Here is the underlying SQL. SELECT Avg(IIf([Grade]="A+",99,IIf([Grade]="A",95 ,IIf([Grade]="A-",91,IIf([Grade]="B+",88 ,IIf([Grade]="B",85,IIf([Grade]="B-",82 ,IIf([Grade]="C+",78,IIf([Grade]="C",75 ,IIf([Grade]="C-",72,IIf([Grade]="D",65 ,IIf([Grade]="F",0,"?")))))))))))) AS GradeNum FROM Grades;

Thanks. I've tried this and also setting each number with the Int(99) and Fix(99) (integer and fixed number format) and I always get the "type mismatch" error in both a second query or report. Something else must be going on behind the scene that I can't see. I exported it to Excel and was able to do the math there so I no longer think it is exporting as text. I have never seen anything like this in Access before.

Are you saying that the query I listed gives Type Mismatch? I'm running Access 2003 on Win XP SP3 with no error. If you're running more queries, it could be something in some other query or calculation.

Are you saying that the query I listed gives Type Mismatch? I'm running Access 2003 on Win XP SP3 with no error. If you're running more queries, it could be something in some other query or calculation.

Thanks. I run the query without the AVG because I am extracting other data for the report. I add the =avg({GradeNum]) as a new field in a report generated by the query. This is where I get the "type mismatch" and the report won't run. If I try to base a second query on the first the same thing happens when I try to apply =avg or =sum to the field [GradeNum]. It's quite strange. It's acting like it is a text field when in fact it exports to Excel as a number. I've never seen anything like this. Update: I created this query and report based on the same table on another computer running Access 2003 instead of 2002 and it worked beautifully.