Too Many IIf Statements

[AimeeK via AccessMonster.com]

Hi, I'm trying to make a query performed a calculated based the Week Number
for each record...basically if the Week Number is this number, divide by this
number. If the Week Number is that number, divide by that number.
Unfortunately, I have 10 different Week Numbers, and I have to divide a
different number for each week.

For example:

If Week = 1, Max Cases/10
If Week = 2, Max Cases/9
If Week =3, Max Cases/8
etc, etc

However, when I tried nesting all 10 iif statements together in the query
expression builder, it cut off my last 2 calculations, so obviously, I
exceeded the limit. I've read about a Switch or Choose function, but I have
no idea how to do either one? Can anyone help? Thanks in advance for your
help.

 

[Tom Ellison]

Dear Aimee:

If the other alternatives follow the pattern of the first 3 you posted, you
might want to try this:

[Max Cases] / (11 - Week)

I have assumed Max Cases is the name of a column and this is a query.
That's why it is inside square brackets like that.

Tom Ellison

 

[AimeeK via AccessMonster.com]

Now, why didn't I think of that? Iol
It worked! Thank you so much!

Dear Aimee:

If the other alternatives follow the pattern of the first 3 you posted, you
might want to try this:

[Max Cases] / (11 - Week)

I have assumed Max Cases is the name of a column and this is a query.
That's why it is inside square brackets like that.

Tom Ellison

Hi, I'm trying to make a query performed a calculated based the Week
Number

[quoted text clipped - 18 lines]

your
help.

 

[Tom Ellison]

Dear Aimee:

This may be only a starting point. I should work well for values 1 to 10.
Can you assure that no other values of Week are possible?

If Week is 11 this will error. You should consider building in some form of
protection for this.

Tom Ellison

Now, why didn't I think of that? Iol
It worked! Thank you so much!

Dear Aimee:

If the other alternatives follow the pattern of the first 3 you posted,
you
might want to try this:

[Max Cases] / (11 - Week)

I have assumed Max Cases is the name of a column and this is a query.
That's why it is inside square brackets like that.

Tom Ellison

Hi, I'm trying to make a query performed a calculated based the Week
Number

[quoted text clipped - 18 lines]

your
help.