If statements and numbers

Microsoft / Access 2003
May 5, 2009 at 08:44:00
Specs: Windows XP
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.

See More: If statements and numbers

Report •


#1
May 6, 2009 at 07:51:21
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;


Report •

#2
May 6, 2009 at 08:16:07
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.

Report •

#3
May 7, 2009 at 06:39:25
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.


Report •

Related Solutions

#4
May 7, 2009 at 07:32:05
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.


Report •

#5
May 7, 2009 at 09:47:16
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.


Report •


Ask Question