Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    64

    Unanswered: Expression exceeds the 1,024-character limit

    My query doens't work after the 13th IIf statement and gives me this message:
    "The expression you entered exceeds the 1,024-character limit for the query design grid".
    How can I resolve this issue?
    Thanks,

    SELECT IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="4" And [qryJOE_REPORT_2.subscription_def] Like "*MV*","MV 30 Days Prior To Expire",
    IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="4" And [qryJOE_REPORT_2.subscription_def] Like "*RH*","RH 30 Days Prior To Expire",

    IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="4" And [qryJOE_REPORT_2.subscription_def] Like "*RE*","RE At Expire",
    IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="4" And [qryJOE_REPORT_2.subscription_def] Like "*CE*","CE At Expire",
    IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="4" And [qryJOE_REPORT_2.subscription_def] Like "*AG*","AG At Expire",
    IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="4" And [qryJOE_REPORT_2.subscription_def] Like "*AC*","AC At Expire",
    IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="4" And [qryJOE_REPORT_2.subscription_def] Like "*RX*","RX At Expire",

    IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="5" And [qryJOE_REPORT_2.subscription_def] Like "*RE*","RE 30 Days After Expire",
    IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="5" And [qryJOE_REPORT_2.subscription_def] Like "*CE*","CE 30 Days After Expire",
    IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="5" And [qryJOE_REPORT_2.subscription_def] Like "*AG*","AG 30 Days After Expire",
    IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="5" And [qryJOE_REPORT_2.subscription_def] Like "*AC*","AC 30 Days After Expire",
    IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="5" And [qryJOE_REPORT_2.subscription_def] Like "*RX*","RX 30 Days After Expire",

    IIf([qryJOE_REPORT_2.MaxOfEffortLevel]="5" And [qryJOE_REPORT_2.subscription_def] Like "*MV*","MV 30 Days After Expire",


    ""))))))))))))) AS ["KeyCodeList"], qryJOE_REPORT_2.Subscription_Def, qryJOE_REPORT_2.MaxOfEffortLevel, qryJOE_REPORT_2.Customer_ID, qryJOE_REPORT_2.CallResult, qryJOE_REPORT_2.RepName
    FROM qryJOE_REPORT_2;

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Take a look at the Switch function.

    Considering the size of that iif statement, you may want to consider a reference table.

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    At first glance, taking away the 1024 character limit error. I am not sure if it would run. To me it looks like:

    Code:
    if(
     if(
      if(
       if(....)
         ...)
       ...)
      ...)
    ...)

  4. #4
    Join Date
    Jan 2004
    Posts
    64
    I didn't realize access had a 1024 character limit. I broke up the query and created a union query afterwards to put the results together.

    Thanks for the posts.

  5. #5
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Does the new query just look the other(s) up?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why not make a keycode table? Obviously you have enough combinations to make it worth your time. I don't know exactly what real-world scenario this is based in, but I can't think of one that wouldn't make sense to have a keycode table.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Smile

    Hi bsarman,

    Looking at the many IIF statements you have why not do the [b]IF/THEN]/B] statements in VBA? OR you could look into SelectCase statements. I haven't gotten too deep into SelectCase statements but did it a while back and will see if I can find it and post a copy for you to practice with. I heard that Nested IIF statements are a pain to manage as well and should really be limited to smaller amounts of choices. I will try to get back to here soon.

    have a nice one,
    Bud

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Cool

    hi bsarman,

    I found it...take a look and see what you think:

    Code:
    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    'Created by BUD 1-26-2004.
    'Reflects the real value of the Priority field. My FIRST Select
    'Case statement ever done. The CONST sets the weight of the fonts
    
        Const conNormal = 400
        Const conHeavy = 900    'Extra bold
        Const conSmall = 8
        Const conLarge = 12
    
    Select Case Priority
        Case 1
            Level = "Low"
            Level.FontWeight = conNormal
            Level.FontSize = conSmall
        Case 2
            Level = "Medium"
            Level.FontWeight = conNormal
            Level.FontSize = conSmall
        Case 3
            Level = "HIGH"
            Level.FontWeight = conHeavy
            Level.FontSize = conLarge
        Case Else
            Level = ""
        
        End Select
        
    End Sub
    On my form I have OptionButtons to select the Priority level for my meeting minutes. Case1 prints the word "LOW" if the first radio button is clicked, next "Medium" then "HIGH" and also sets the font SIZE and WEIGHT. Go over it and see if it makes sense to you and are able to work it into your on situation. Just trying to help.

    Bud

  9. #9
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would put all the logic into a procedure. Then in the query call the procedure with:

    KeyCodeList:GetKeyCode([MaxOfEffortLevel],[subscription_def])

    GetKeyCode would be the procedure name and in the procedure use the InStr function to see if RE, CE... are in the string.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by DCKunkle
    I would put all the logic into a procedure. Then in the query call the procedure with:

    KeyCodeList:GetKeyCode([MaxOfEffortLevel],[subscription_def])

    GetKeyCode would be the procedure name and in the procedure use the InStr function to see if RE, CE... are in the string.
    That's the same logic as what I"m suggesting... wouldn't it be cleaner if the criteria were stored in a table instead of a static function?

    What happens if the criteria changes, or new criteria is added?

    Referencing the keycode through a basic inner join is the way to go IMO.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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