jfakes-
I assume all the values for the various percentile rates are numbers, so the
expression expects a number, but you're returning a string for the last value.
You could try:
2010NextPercentileRateTESTTEST: IIf([ReportingYear]=2010,IIf([Rate]>=[201010th]
And [Rate]<[201025th],CStr([201025th]),IIf([Rate]>=[201025th] And
[Rate]<[201050th],CStr([201050th]),IIf([Rate]>=[201050th] And
[Rate]<[201075th],CStr([201075th]),IIf([Rate]>=[201075th] And
[Rate]<[201090th],CStr([201090th]),IIf([Rate]>=[201090th],"N/A"))))))
That returns a string for all values.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of jfakes.rm
Sent: Thursday, May 19, 2011 8:55 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] #Error when running mulitple IIfs in a query
I have a requirement to display a current percentile ranking, and then the next
higher percentile range. For example
If the current ranking is in the 50th percentile and the rate is 12.1, the user
wants the rate for the 75th percentile say its 14. To grab the percentile
rankings, I used this query (and it works):
2010Percentile: IIf([ReportingYear]=2010,IIf([Rate]>=[201010th] And
[Rate]<[201025th],"10th",IIf([Rate]>=[201025th] And
[Rate]<[201050th],"25th",IIf([Rate]>=[201050th] And
[Rate]<[201075th],"50th",IIf([Rate]>=[201075th] And
[Rate]<[201090th],"75th",IIf([Rate]>=[201090th],"90th"))))))
However, the next step is to display the actual rate. When you have a rate in
the 90th percentile, the user wants "N/A" display as there is not a higher rate.
However, I keep getting an #Error only for the 90th percentile rate when I run
the below query. The rest of the rates display perfectly:
2010NextPercentileRateTESTTEST: IIf([ReportingYear]=2010,IIf([Rate]>=[201010th]
And [Rate]<[201025th],[201025th],IIf([Rate]>=[201025th] And
[Rate]<[201050th],[201050th],IIf([Rate]>=[201050th] And
[Rate]<[201075th],[201075th],IIf([Rate]>=[201075th] And
[Rate]<[201090th],[201090th],IIf([Rate]>=[201090th],"N/A"))))))
I know there is probably a smoother way of doing this, however, I have never had
to build such a complex report.
Any suggestions on why I get the #Error displayed where ever there is a 90th
percentile rate?
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar