Masking a field that returns "#error" when certain conditions are not met

[manix]

Hi All,

I have a complex query that uses the following expression to calculate a rating. However, if one of the ocnditions is not met, it returns a "#error". This is fine, but I want to mask this with something like "No Rating", when they are not met.

Is this possible and how is best to do it? I am not sure if I can fit anymore elses into the expression. Is it best to put it via VB>?

Rating: IIf([qry_vend_rating2_lstQ2]![Total Demerits]<=10,"A",IIf([qry_vend_rating2_lstQ2]![Total Demerits]<=30,"B",IIf([qry_vend_rating2_lstQ2]![Total Demerits]<=50,"C",IIf([qry_vend_rating2_lstQ2]![Total Demerits]>=51,"D","A"))))

So if none of these conditions are met, you will get #error.

TIA

 

[MSAccessRookie]

Re: Masking a field that returns "#error" when certain conditions are not met

Hi All,

I have a complex query that uses the following expression to calculate a rating. However, if one of the ocnditions is not met, it returns a "#error". This is fine, but I want to mask this with something like "No Rating", when they are not met.

Is this possible and how is best to do it? I am not sure if I can fit anymore elses into the expression. Is it best to put it via VB>?

Rating: IIf([qry_vend_rating2_lstQ2]![Total Demerits]<=10,"A",IIf([qry_vend_rating2_lstQ2]![Total Demerits]<=30,"B",IIf([qry_vend_rating2_lstQ2]![Total Demerits]<=50,"C",IIf([qry_vend_rating2_lstQ2]![Total Demerits]>=51,"D","A"))))

So if none of these conditions are met, you will get #error.

TIA

Using this Formula, the only possibility left to account for is Null. Assing another IIf to check for Null Values should be what you need. The "No Rating" at the end is there to complete the IIf and should never be true according to the rules of the query.

[COLOR=black][FONT=Times New Roman][SIZE=3][COLOR=red][B]IIf([qry_vend_rating2_lstQ2]![Total Demerits] Is Null, "No Rating", [/B][/COLOR][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3]   IIf([qry_vend_rating2_lstQ2]![Total Demerits] <= 10, "A", [/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3]       IIf([qry_vend_rating2_lstQ2]![Total Demerits] <= 30, "B", [/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3]           IIf([qry_vend_rating2_lstQ2]![Total Demerits] <= 50, "C", [/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3]               IIf([qry_vend_rating2_lstQ2]![Total Demerits] >= 51, "D", [COLOR=red][B]"No Rating")[/B][/COLOR]))))[/SIZE][/FONT][/COLOR]

 

[manix]

Re: Masking a field that returns "#error" when certain conditions are not met

Using this Formula, the only possibility left to account for is Null. Assing another IIf to check for Null Values should be what you need. The "No Rating" at the end is there to complete the IIf and should never be true according to the rules of the query.

[COLOR=black][FONT=Times New Roman][SIZE=3][COLOR=red][B]IIf([qry_vend_rating2_lstQ2]![Total Demerits] Is Null, "No Rating", [/B][/COLOR][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3]   IIf([qry_vend_rating2_lstQ2]![Total Demerits] <= 10, "A", [/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3]       IIf([qry_vend_rating2_lstQ2]![Total Demerits] <= 30, "B", [/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3]           IIf([qry_vend_rating2_lstQ2]![Total Demerits] <= 50, "C", [/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3]               IIf([qry_vend_rating2_lstQ2]![Total Demerits] >= 51, "D", [COLOR=red][B]"No Rating")[/B][/COLOR]))))[/SIZE][/FONT][/COLOR]

Thanks for the quick response, but it still produces the #error and not the "No Rating".

Umm, think this might get complicated. The expression works when there is data, but when there is no data avialble from the connected queries, then it seems to have this error?!

 

[DCrake]

Re: Masking a field that returns "#error" when certain conditions are not met

You may be better creating a function to handle this. Place the following in an existing or new module

Public Function GetRating(AnyValue As Integer) As String

Select Case AnyValue
     Case 0 :GetRating = "No Rating"
     Case < 11: GetRating = "A"
     Case < 31: GetRating = "B"
     Case < 51: GetRating = "C"
     Case Else :GetRating = "D"
End Select

End Function

Then in your query use

MyRating:GetRating(Nz(YourFieldName,0))

David