Looking for suggestions on Long IIF statment

[Dormie]

Hi, I was wondering if anyone could give me a suggestion or if there is a way to make my IIF statement cleaner.

Basically, I have a bunch of numbers in a single field I'm looking to round based on their value. For example, I'm rounding below 1 to 1, below 5 to 5 and so on. I'm looking at probably not the longest iif statement ever made but long for me. Plus, it's very hard to read incase I want to modify....



Is this something that could be handled by VBA (I don't really have any experience in)? Maybe there is a way to do it cleaner in Excel? I'm not really too sure what my best avenue is but hoping for a couple suggestions over the long statement in expression builder.

Thanks in advance!

[pbaldy]

I'm not clear on what you're trying to achieve, but I'd probably opt for a VBA function over a long, nested formula. Maybe this gets you started:

http://www.baldyweb.com/Function.htm

[June7]

Consider:

Switch([field]<=1,1, [field]<=5,5, [field]<=10,10)

How high do the numbers go? For numbers greater than 1, do you want to round up to multiple of 5?

Otherwise, as Paul suggests, anything more complicated will likely require a custom function.

[orange]

Suggest you step back and tell us in plain English about the "business" that this database will support.
Just as you would tell someone who knows nothing of you, your environment nor database.
No jargon.

Good luck.

[Dormie]

Thanks June7 & pblady, the more I look into it. It seems the custom function should do the job. I wasn't really aware of it before and I'm sure exactly what I'm doing but I'm learning.

[pbaldy]

No problem, post back if you get stuck creating one.

[CJ_London]

if your rounding points are in steps of 5, consider the partition function (although<1 would be to be treated separately). Perhaps something like

val(iif(field<=1,"1",mid(partition(field,1,999,5), instr(partition(field,1,999,5),":")+1)))

change 999 to any max number to meet your needs - the above will return a 0 if your field value exceeds it