Jumat, 20 Mei 2011

RE: [MS_AccessPros] #Error when running mulitple IIfs in a query

 


For some reason, this message was still in my "Drafts" folder. Excuse me if this is a duplicate message.

I think I might have replaced the "N/A" with Null and then used the Format property of the control in the report to display N/A when the bound value is Null. I don't care for forcing a string when it should be a number :-( I'll hold my tongue regarding the table structure :-)

Duane Hookom
MS Access


From: john@viescas.com

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-----
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of jfakes.rm

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?



[Non-text portions of this message have been removed]

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar