IIf statement too long. Other options?

[Christina]

In my query I have a field with an iif statement that is too long now.

simple ex.
IIf([region]="LA",([date]-[daysLA])-[additionaldaysLA],
IIf([region]="NA",([date]-[daysNA])-[additionaldaysNA],([date]-[days])-
[additionaldays]))

Is there any way to convert this to a function to call from the query?

 

[Daryl S]

Christina -

Yes, you can create a public function to do this, or if you just need to
shorten it a little, you can use this instead:

=[date]-IIf([region]="LA",[daysLA]+[additionaldaysLA],
IIf([region]="NA",[daysNA]+[additionaldaysNA],[days]+[additionaldays]))

If you want it in a public function, then you will need to pass in all
values and return just the one date.

 

[John W. Vinson]

In my query I have a field with an iif statement that is too long now.

simple ex.
IIf([region]="LA",([date]-[daysLA])-[additionaldaysLA],
IIf([region]="NA",([date]-[daysNA])-[additionaldaysNA],([date]-[days])-
[additionaldays]))

Is there any way to convert this to a function to call from the query?

What's the context, Christina? Do you have a table with many Fields for
various days and additionaldays values as fields? If so, you're "committing
spreadsheet"; consider a tall-thin table with a field for Region, a field for
Days, and a field for Additionaldays. You could then simply use a join to this
table to look up the values, with no IIF statement at all.

 

[Christina]

What's the context, Christina? Do you have a table with many Fields for
various  days and additionaldays values as fields? If so, you're "committing
spreadsheet"; consider a tall-thin table with a field for Region, a fieldfor
Days, and a field for Additionaldays. You could then simply use a join tothis
table to look up the values, with no IIF statement at all.

Thank you!!!
I think you're correct with committing spreadsheet. darn it!
I do need to go tall and skinny and look it up the table that way.

 

[John W. Vinson]

Thank you!!!
I think you're correct with committing spreadsheet. darn it!
I do need to go tall and skinny and look it up the table that way.

Ego absolvo te, filia mea; go and sin no more!