If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using criteria on a calculated field in a query
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. -- jubu |
#2
|
|||
|
|||
Using criteria on a calculated field in a query
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 -- Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "jubu" wrote: 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. -- jubu |
Thread Tools | |
Display Modes | |
|
|