Nested iif using AND (1 Viewer)

[Geordie2008]

Hi All,

I cant find the answer tp this one using the search, although Im v sure it must be a common problem.....

Im trying to do the following.

If [Limit] = 0 then "No Limit"
If [Limit] > 0 < or = to 1000 then "Limit up to £1,000"
If [Limit]>1000< or = to 5000 then "Limit between 1,000 and 5,000"
Else "Limit greater than 5,000"

Can anyone help me define the syntax for such a query?

Reason3: IIf([Exceptions_LGDR1].[Net Marked Limit GBP]=0,"No Limit",iif([Exceptions_LGDR1].[Net Marked Limit GBP]>0 AND <=1000,"Limit up to 1,000",iif([Exceptions_LGDR1].[Net Marked Limit GBP]>1000 AND <=5000,"Limit between 1,000 and 5,000","Limit greater than 5,000")))

Thanks,
M

 

[KenHigg]

Reason3: IIf([Exceptions_LGDR1].[Net Marked Limit GBP]=0,"No Limit",iif([Exceptions_LGDR1].[Net Marked Limit GBP] <=1000,"Limit up to 1,000",iif([Exceptions_LGDR1].[Net Marked Limit GBP] <=5000,"Limit between 1,000 and 5,000","Limit greater than 5,000")))

???

 

[Geordie2008]

Ah, of course, if you do it in order you dont need the other restrictions......

Thanks, brain is not functioning logically today then....

M

 

[DCrake]

Nested IIF's are ok as long as you don't have many options. You may be better managing this task using a function. such as:

Public function ValueBand(AnyValue As Long) As String

Select Case AnyValue
   Case 0 :ValueBand = "No Limit"
   Case < 1000 : ValueBand = "Upto £1000"
   Case < 5000 :ValueBand = "£1000 - £5000"
   Case Else :ValueBand = "Over £5000"
EndSelect

End Function

Save this function in a module. Then in your query use Band:ValueBand([YourFieldName])

Then if you need to modify your boundaries you simply modify your function accordingly. The benefit of doing this way is that you can use this function wherever you want in your app. Should you need to revise it you only do it once in the module not in every location it has been employed. This is known as Least Adminitrative Effort.


David