Using criteria on a calculated field in a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jubu - 16 Apr 2008 18:20 GMT

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.

Signature

jubu

Reply to this Message

Dale Fye - 16 Apr 2008 20:02 GMT

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

Signature

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

> 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.
[quoted text clipped - 15 lines]
> the other two. Any ideas? All suggestions are very much appreciated, as I
> continue to learn this program.

Reply to this Message