IIf statement too long. Other options?

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?

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.

--
Daryl S


"Christina" wrote:

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?


.

On Thu, 3 Dec 2009 06:15:10 -0800 (PST), Christina wrote:

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.
--

John W. Vinson [MVP]

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.
--

* * * * * * *John W. Vinson [MVP]



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.

On Thu, 3 Dec 2009 10:07:01 -0800 (PST), Christina wrote:


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.
--

* * * * * * *John W. Vinson [MVP]



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!
--

John W. Vinson [MVP]