Using criteria on a calculated field in a query

[jubu]

I have an Access 2003 database. The main table get its data from another
system (downloaded) of which some of the number fields come through as text.

Class (refers to number of students in graduating class)
Rank (refers to the students' ranking position in graduating class)

I created a new field in my query for each to "convert" the above to numeric
values:

ClassValue: Val([class])
RankValue: Val([rank])

A third calculated field is created to determine the percentage: Top%:
IIf([ClassValue]=0 And [RankValue]=0,0,[RankValue]/[ClassValue])
When I attempt to put criteria in this Top% field, I get parameters looking
for Top%, ClassValue, and RankValue. I set the Format property for Top% to
Percent, and that eliminated the parameter for Top% but I am still getting
the other two. Any ideas? All suggestions are very much appreciated, as I
continue to learn this program.

 

[Dale Fye]

You are not going to be able to use the aliased fields in the criteria
statement of your query.

You really don't need to test for [Rank] = 0. BTW, I wouldn't use special
characters in field names, it confuses Access. If you must, encapsulate the
field name in brackets [ ] whenever you use that field name.

TopPct: IIF(Val([Class])=0, 0, Val([Rank])/Val([Class])

Also, is there any chance that the field will be NULL? If so you should
account for that in your query.

TopPct: IIF(Val(NZ([Class], "0"))=0, 0, Val(NZ([Rank],"0"))/Val(NZ([Class],
"0")))

HTH
Dale