Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Number of characters in a cell in the query design grid

    I have a query as the source of a report which I developed using the design grid... in one of the fields I have the following code :Process Point: IIf([Cams_Change_Data1]![STATUS_CODE]="D" Or [Cams_Change_Data1]![STATUS_CODE]="E" Or [Cams_Change_Data1]![STATUS_CODE]="O" Or [Cams_Change_Data1]![STATUS_CODE]="P" Or [Cams_Change_Data1]![STATUS_CODE]="S","At EB",IIf([Cams_Change_Data1]![STATUS_CODE]="F" Or [Cams_Change_Data1]![STATUS_CODE]="G" Or [Cams_Change_Data1]![STATUS_CODE]="H" Or [Cams_Change_Data1]![STATUS_CODE]="I" Or [Cams_Change_Data1]![STATUS_CODE]="J" Or [Cams_Change_Data1]![STATUS_CODE]="L" Or [Cams_Change_Data1]![STATUS_CODE]="M" Or [Cams_Change_Data1]![STATUS_CODE]="R" Or [Cams_Change_Data1]![STATUS_CODE]="W" Or [Cams_Change_Data1]![STATUS_CODE]="X" Or [Cams_Change_Data1]![STATUS_CODE]="Y" Or [Cams_Change_Data1]![STATUS_CODE]="Z","AT SUPSHIP",IIf([Cams_Change_Data1]![STATUS_CODE]="A" Or [Cams_Change_Data1]![STATUS_CODE]="B" Or [Cams_Change_Data1]![STATUS_CODE]="C" Or [Cams_Change_Data1]![STATUS_CODE]="Q" Or [Cams_Change_Data1]![STATUS_CODE]="V" Or [Cams_Change_Data1]![STATUS_CODE]="K","IN 400",IIf([Cams_Change_Data1]![STATUS_CODE]="N","IN NEGOTIAIONS","UNSOLICITED"))))which with other query requirements causes it to exceed the 1024 character limitthe entire SQL for this query should look like the following:SELECT IIf(Left$([NEGOTIATOR_CODE],3) Between "420" And "429","420 - NEW CONSTRUCTION",IIf(Left$([NEGOTIATOR_CODE],3) Between "450" And "459","450 - REPAIR","460 - DESIGN")) AS [NEGOTIATOR CODE], Cams_Change_Data1.PIIN_CODE, Cams_Change_Data1.PIIN, Cams_Change_Data1.SOSID_NUM, IIf([Cams_Change_Data1]![STATUS_CODE]="D" Or [Cams_Change_Data1]![STATUS_CODE]="E" Or [Cams_Change_Data1]![STATUS_CODE]="O" Or [Cams_Change_Data1]![STATUS_CODE]="P" Or [Cams_Change_Data1]![STATUS_CODE]="S","At EB",IIf([Cams_Change_Data1]![STATUS_CODE]="F" Or [Cams_Change_Data1]![STATUS_CODE]="G" Or [Cams_Change_Data1]![STATUS_CODE]="H" Or [Cams_Change_Data1]![STATUS_CODE]="I" Or [Cams_Change_Data1]![STATUS_CODE]="J" Or [Cams_Change_Data1]![STATUS_CODE]="L" Or [Cams_Change_Data1]![STATUS_CODE]="M" Or [Cams_Change_Data1]![STATUS_CODE]="R" Or [Cams_Change_Data1]![STATUS_CODE]="W" Or [Cams_Change_Data1]![STATUS_CODE]="X" Or [Cams_Change_Data1]![STATUS_CODE]="Y" Or [Cams_Change_Data1]![STATUS_CODE]="Z","AT SUPSHIP",IIf([Cams_Change_Data1]![STATUS_CODE]="A" Or [Cams_Change_Data1]![STATUS_CODE]="B" Or [Cams_Change_Data1]![STATUS_CODE]="C" Or [Cams_Change_Data1]![STATUS_CODE]="Q" Or [Cams_Change_Data1]![STATUS_CODE]="V" Or [Cams_Change_Data1]![STATUS_CODE]="K","IN 400",IIf([Cams_Change_Data1]![STATUS_CODE]="N","IN NEGOTIAIONS","UNSOLICITED")))) AS [Process Point], Cams_Change_Data1.STATUS_CODE, Lookup.[Status Description], Cams_Change_Data1.CHG_REC_VALUE, Abs([Cams_Change_Data1]![CHG_REC_VALUE]) AS [ABS Record Value]FROM Cams_Change_Data1 INNER JOIN Lookup ON Cams_Change_Data1.STATUS_CODE = Lookup.[Status Code]WHERE (((Cams_Change_Data1.STATUS_CODE)"T" And (Cams_Change_Data1.STATUS_CODE)"U"));is there a way to shorten the reference to "[Cams_Change_Data1]![STATUS_CODE]" where it repeats so many times?
    Kevin

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    You might consider using the IN() construct rather than doing so many ="x" clauses, i.e. IIf([Cams_Change_Data1]![Status_Code] In("D","E","O","P","S"),"At EB",.....).
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I don't think you can use IN in that situation.

    Rather than nested IIF statements, I think you should investigate the Switch function. It is much easier to use and read than all those nested IIFs.

    However, why not create function to handle this? That opens up all sorts of options (like using Select-Case, etc.) that will be a lot easier to read and change later (if necessary).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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