Too Many Characters in Query

[Littlemalky]

I have this really long formula, but just realized that I need to set negative values to zero. If I put an "IIF Statement" around it, Access tells me there are too many characters. Does anybody have any suggestions?

IIf((IIf(([PAYOUT_DATE]>=[GUARANTEE_START_DATE] And [PAYOUT_DATE]<=[GUARANTEE_END_DATE]),[GUARANTEE_AMOUNT],0))*(IIf(IIf(Day([PAYOUT_DATE])>=Day([GUARANTEE_START_DATE]),0,-1)+(Year([PAYOUT_DATE])-Year([GUARANTEE_START_DATE]))*12+(Month([PAYOUT_DATE])-Month([GUARANTEE_START_DATE]))<4,1,0))=0,[PAYOUT_AMT],(IIf(([PAYOUT_DATE]>=[GUARANTEE_START_DATE] And [PAYOUT_DATE]<=[GUARANTEE_END_DATE]),[GUARANTEE_AMOUNT],0))*(IIf(IIf(Day([PAYOUT_DATE])>=Day([GUARANTEE_START_DATE]),0,-1)+(Year([PAYOUT_DATE])-Year([GUARANTEE_START_DATE]))*12+Month([PAYOUT_DATE])-Month([GUARANTEE_START_DATE])<4,1,0)))

 

[ranman256]

You really shouldn't use that many IIFs in a query. Instead , either use separate queries,...1 to do one condition,then 1 to do another.
or
use a custom Formula to do the IF,THENs.

 

[Littlemalky]

Thanks for responding. What do you mean by a custom formula?

 

[stumac]

Creating a custom function to do the calculations rather than using all the IIF's in the query.

Can you explain what you are trying to achieve with the formula - there could be several other ways to do it.

 

[xenou]

You could also do such things as:
work with nested or stepwise query results to manipulate your results
break the results into multiple fields that can be easily handled in the next step
use shorter field names
put some of your conditions in the where clause to avoid so many IIFs

 

[james_lankford]

I haven't looked in detail at those "if statements" but it seems like the datediff function might be useful and could maybe shorten it

https://www.techonthenet.com/access/functions/date/datediff.php

https://www.w3schools.com/sql/func_msaccess_datediff.asp

 

[Littlemalky]

So basically what I'm trying to accomplish is that each one of my sales people have 6 month commission guarantees. However, in month's 5 and 6 they get the greater of the two (whether the calculated [PAYOUT_AMT] is higher or the [GUARANTEE_AMOUNT] is higher). I store their guarantee information in a table that consists of their [EMPLOYEE_ID] (which I'm joining on), [GUARANTEE_START_DATE], [GUARANTEE_END_DATE], and [GUARANTEE_AMOUNT]. The [PAYOUT_DATE] is the last day of the prior month (the month I'm paying commissions for). So I need this to dynamically calculate which amount the person gets (the PAYOUT_AMT vs. GUARANTEE_AMOUNT). Also, it needs to be able to cross-over years and still figure out if it's within month's 5 and 6.

 

[stumac]

As James says the DateDiff function should shorten your expression and remove the requirement to deal with negatives. You should be able to build on something like:

 IIf(DateDiff("m",[GUARANTEE_START_DATE],[PAYOUT_DATE])>4,IIf([PAYOUT_AMT]>[GUARANTEE_AMOUNT],[PAYOUT_AMT],[GUARANTEE_AMOUNT]),[PAYOUT_AMT])

This will calculate the months between 2 dates even if it spans multiple years.