John,
That worked perfectly thanks so much. As far as the table design, don't even get me started on that. I inherited the database and an external vendor sends standard updates out that I can't control or change.
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> 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