Number of characters in a cell in the query design grid

Topic

New Reply

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?

Reply | Quote

Replies

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”,…..).

Reply | Quote

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).

Reply | Quote