Results 1 to 5 of 5
  1. 07-04-2010, 10:58 AM #1
    dref
    dref is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    55

    String returned by the builder was too long.

    Hi
    When i try to paste the statement(more debtordays and sections not included) below in a percentage field in my query it returns:String returned by the builder was too long.result will be truncated. OR the expression you entered is too complex. What should i do?
    Thanks, Fred.

    IIf([Section]="Supplements" And [DebtorDays]>=90,"0.07",IIf([Section]="Supplements" And [DebtorDays]>=61,"0.09",IIf([Section]="Supplements" And [DebtorDays]>=46,"0.11",IIf([Section]="Supplements" And [DebtorDays]>=31,"0.16",IIf([Section]="Supplements" And [DebtorDays]>=8,"0.19",IIf([Section]="Supplements" And [DebtorDays]>=0,"0.20",IIf([Section]="Display" And [DebtorDays]>=90,"0.07",IIf([Section]="Display" And [DebtorDays]>=61,"0.09",IIf([Section]="Display" And [DebtorDays]>=46,"0.10",IIf([Section]="Display" And [DebtorDays]>=31,"0.12",IIf([Section]="Display" And [DebtorDays]>=8,"0.16",IIf([Section]="Display" And [DebtorDays]>=0,"0.18",IIf([Section]="CRM" And [DebtorDays]>=90,"0.07",IIf([Section]="CRM" And [DebtorDays]>=61,"0.09",IIf([Section]="CRM" And [DebtorDays]>=46,"0.10",IIf([Section]="CRM" And [DebtorDays]>=31,"0.12",IIf([Section]="CRM" And [DebtorDays]>=8,"0.16",IIf([Section]="CRM" And [DebtorDays]>=0,"0.18","0"))))))))))))))))))
    Reply With Quote Reply With Quote
  2. 07-04-2010, 11:22 PM #2
    dsmacs
    dsmacs is offline Advanced Beginner
    Windows XP Access 2010 (version 14.0)
    Join Date
    Oct 2009
    Location
    Perth Western Australia
    Posts
    52
    Hi Fred,

    One option would to have a table with the % and days options for your various sections and link the Section ID and the Debtor days ranges. This will enable changes to the Section types as well as each % to be more easilier changed .

    Other option would to have a function to calculate the percentage;-

    Function GetPercent(Sec As String, Ddays As Single)
    If Sec = "Supplements" Then

    If Ddays >= 90 Then
    GetPercent = 0.07
    ElseIf Ddays >= 91 Then
    GetPercent = 0.09
    ElseIf Ddays >= 46 Then
    GetPercent = 0.11
    ElseIf Ddays >= 31 Then
    GetPercent = 0.16
    ElseIf Ddays >= 8 Then
    GetPercent = 0.19
    ElseIf Ddays >= 0 Then
    GetPercent = 0.2
    End If

    ElseIf Sec = "Display" Then

    If Ddays >= 90 Then
    GetPercent = 0.07
    ElseIf Ddays >= 91 Then
    GetPercent = 0.09
    ElseIf Ddays >= 46 Then
    GetPercent = 0.11
    ElseIf Ddays >= 31 Then
    GetPercent = 0.16
    ElseIf Ddays >= 8 Then
    GetPercent = 0.19
    ElseIf Ddays >= 0 Then
    GetPercent = 0.2
    End If

    ElseIf Sec = "CRM" Then

    If Ddays >= 90 Then
    GetPercent = 0.07
    ElseIf Ddays >= 91 Then
    GetPercent = 0.09
    ElseIf Ddays >= 46 Then
    GetPercent = 0.11
    ElseIf Ddays >= 31 Then
    GetPercent = 0.16
    ElseIf Ddays >= 8 Then
    GetPercent = 0.19
    ElseIf Ddays >= 0 Then
    GetPercent = 0.2
    End If
    End If
    End Function



    The formula in your query would be;-

    GetPercent ([Section],[DebtorDays])



    Cheers
    Darren
    Last edited by dsmacs; 07-04-2010 at 11:35 PM. Reason: Sent prematurely, didn't complete.
    Reply With Quote Reply With Quote
  3. 07-15-2010, 11:40 PM #3
    dref
    dref is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    55
    Thanks Darren.
    i am not an expert user. Am not good at using modules, i restrict myself to queries and forms. If am using a form to do this how do i go about it.
    What do i do in the events to get the desired results. That is if the form is to do every thing and i only get the Ddays from a query.
    could something like this be modified to help?

    Private Sub Sec_AfterUpdate()
    If Sec = "Supp" Then
    If DD >= 90 Then
    GetPerc = 0.07
    ElseIf DD >= 91 Then
    GetPerc = 0.09
    ElseIf DD >= 46 Then
    GetPerc = 0.11
    ElseIf DD >= 31 Then
    GetPerc = 0.16
    ElseIf DD >= 8 Then
    GetPerc = 0.19
    ElseIf DD >= 0 Then
    GetPerc = 0.2
    End If
    ElseIf Sec = "Disp" Then
    If DD >= 90 Then
    GetPerc = 0.07
    ElseIf DD >= 91 Then
    GetPerc = 0.09
    ElseIf DD >= 46 Then
    GetPerc = 0.11
    ElseIf DD >= 31 Then
    GetPerc = 0.16
    ElseIf DD >= 8 Then
    GetPerc = 0.19
    ElseIf DD >= 0 Then
    GetPerc = 0.2
    End If
    ElseIf Sec = "CRM" Then
    If DD >= 90 Then
    GetPerc = 0.07
    ElseIf DD >= 91 Then
    GetPerc = 0.09
    ElseIf DD >= 46 Then
    GetPerc = 0.11
    ElseIf DD >= 31 Then
    GetPerc = 0.16
    ElseIf DD >= 8 Then
    GetPerc = 0.19
    ElseIf DD >= 0 Then
    GetPerc = 0.2
    End If
    End If
    End Sub

    Thanks
    Fred
    Reply With Quote Reply With Quote
  4. 07-16-2010, 12:33 AM #4
    dref
    dref is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    55
    Darren,
    see this below: It might guide you to help me.
    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:

    Code:
    	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.
    Reply With Quote Reply With Quote
  5. 07-28-2010, 11:25 AM #5
    dref
    dref is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    55
    Hi Darren,
    I have tried the option but when i go to the query and enter GetPercent ([Section],[DebtorDays]) it returns an error messege-'Syntax Error(comma)in Query expression '([Section],[DebtorDays])'.
    Please advise.
    thanks
    Fred


    Quote Originally Posted by dsmacs View Post
    Hi Fred,

    One option would to have a table with the % and days options for your various sections and link the Section ID and the Debtor days ranges. This will enable changes to the Section types as well as each % to be more easilier changed .

    Other option would to have a function to calculate the percentage;-

    Function GetPercent(Sec As String, Ddays As Single)
    If Sec = "Supplements" Then

    If Ddays >= 90 Then
    GetPercent = 0.07
    ElseIf Ddays >= 91 Then
    GetPercent = 0.09
    ElseIf Ddays >= 46 Then
    GetPercent = 0.11
    ElseIf Ddays >= 31 Then
    GetPercent = 0.16
    ElseIf Ddays >= 8 Then
    GetPercent = 0.19
    ElseIf Ddays >= 0 Then
    GetPercent = 0.2
    End If

    ElseIf Sec = "Display" Then

    If Ddays >= 90 Then
    GetPercent = 0.07
    ElseIf Ddays >= 91 Then
    GetPercent = 0.09
    ElseIf Ddays >= 46 Then
    GetPercent = 0.11
    ElseIf Ddays >= 31 Then
    GetPercent = 0.16
    ElseIf Ddays >= 8 Then
    GetPercent = 0.19
    ElseIf Ddays >= 0 Then
    GetPercent = 0.2
    End If

    ElseIf Sec = "CRM" Then

    If Ddays >= 90 Then
    GetPercent = 0.07
    ElseIf Ddays >= 91 Then
    GetPercent = 0.09
    ElseIf Ddays >= 46 Then
    GetPercent = 0.11
    ElseIf Ddays >= 31 Then
    GetPercent = 0.16
    ElseIf Ddays >= 8 Then
    GetPercent = 0.19
    ElseIf Ddays >= 0 Then
    GetPercent = 0.2
    End If
    End If
    End Function



    The formula in your query would be;-

    GetPercent ([Section],[DebtorDays])



    Cheers
    Darren
    Reply With Quote Reply With Quote
Please reply to this thread with any new information or opinions.
« Previous Thread | Next Thread »

Similar Threads

  1. Epression Builder
    By ehrendreich in forum Programming
    Replies: 1
    Last Post: 12-26-2009, 02:33 PM
  2. Code Builder
    By nkenney in forum Forms
    Replies: 3
    Last Post: 11-04-2009, 10:58 AM
  3. Accessing Query Criteria in a Report (when no data is returned)
    By CGM3 in forum Reports
    Replies: 6
    Last Post: 09-25-2009, 12:40 PM
  4. Counting returned records in a query
    By johncob in forum Queries
    Replies: 0
    Last Post: 02-11-2009, 05:30 PM
  5. Expression Builder
    By mistaken_myst in forum Access
    Replies: 2
    Last Post: 05-07-2008, 01:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Other Forums: Microsoft Office Forums