IIf Statement

[dref]

I am using this to compute commission using a ms access query but i get,'String returned by the builder was too long.result will be truncated. OR the expression you entered is too complex. '. how can i use a form to get the desired results.

IIf([Sec]="Supp" And [DD]>=90,"0.07",IIf([Sec]="Supp" And [DD]>=61,"0.09",IIf([Sec]="Supp" And [DD]>=46,"0.11",IIf([Sec]="Supp" And [DD]>=31,"0.16",IIf([Sec]="Supp" And [DD]>=8,"0.19",IIf([Sec]="Supp" And [DD]>=0,"0.20",IIf([Sec]=" Disp" And [DD]>=90,"0.07",IIf([Sec]="Disp" And [DD]>=61,"0.09",IIf([Sec]="Disp" And [DD]>=46,"0.10",IIf([Sec]="Disp" And [DD]>=31,"0.12",IIf([Sec]="Disp" And [DD]>=8,"0.16",IIf([Sec]="Disp" And [DD]>=0,"0.18",IIf([Sec]="CRM" And [DD]>=90,"0.07",IIf([Sec]="CRM" And [DD]>=61,"0.09",IIf([Sec]="CRM" And [DD]>=46,"0.10",IIf([Sec]="CRM" And [DD]>=31,"0.12",IIf([Sec]="CRM" And [DD]>=8,"0.16",IIf([Sec]="CRM" And [DD]>=0,"0.18", IIf([Sec]="BizCtr" And [DD]>=90,"0.07",IIf([Sec]="BizCtr" And [DD]>=61,"0.09",IIf([Sec]="BizCtr" And [DD]>=46,"0.10",IIf([Sec]="BizCtr" And [DD]>=31,"0.12",IIf([Sec]="BizCtr" And [DD]>=8,"0.16",IIf([Sec]="BizCtr" And [DD]>=0,"0.18","0"))))))))))))))))))))))))

Note:


1.DD are debtor days which i calculate in a query(difference between invdate & Rctdate).
2. fields used-StaffCode,StaffName,Section(sec),InvDate,InvAmount ,RctDate,RctAmountDD,percentage(perc),Com(commissi on)
3.In sections(sec) i have Disp,Supp,CRM,KFM,BizCtr,Upctry,....
4. See ranges below:

	Days	Disp	 Supp	CRM
1	Upfront	20%	17%	20%
2	8-30 days	18%	15%	19%
3	31-45 days 	16%	12%	17%
4	46-60 days 	14%	10%	15%
5	61-90days 	12%	8%	12%

i need results in the perc field.


thanks
Fred-Uganda.

[NoellaG]

Hi dref,

when the expression becomes to long/complex you can write your own function in VBA and use this in your query. In this case it would look like:

Public function myCalculation(strSec as string, dblDD as double) as double

dim dblresult as double

select case strSec
case "Supp"
if dblDD >= 90
dblResult = 0.07
elseif dblDD >= 61
dblResult = 0.09
.......
.... "write here other elseif's .............
end if
case "Disp"
if dblDD >= 90
.........
.... continue with other cases...........
..............
end select


myCalculation = dblResult

end function


Of course, you'll have to enter some error handling to your function

succes
NG

[dref]

Thanks NG
Let me try that. i'll post u in. please don't tire helping me when am stranded, am not an expert when it comes to VB.
Thanks gain.