IIf statement too long. Other options?

C

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?
 
D

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

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

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

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top