Allen Browne Article - Expression Exceeding 255 Characters

[purceld2]

Allen Browne Truncation of Memo Fields article indicates if your expression is longer than 255 Characters Memo fields will be truncated. Will the SQL code below contravene this rule does it include table names

SELECT DISTINCT RPT_CCC_PROJECTS_DIM.PROJECT_CODE AS [Clarity Ref], RPT_CCC_PROJECTS_DIM.PARENTPROJECT, RPT_CCC_PROJECTS_DIM.BU_PRIORITY AS [Priority Score], RPT_CCC_PROJECTS_DIM.BU_UNIQUE_REFERENCE AS [CPN Ref], RPT_CCC_PROJECTS_DIM.PROJECT_TITLE AS [Project Title], RPT_CCC_PROJECTS_DIM.INITIATOR AS [Consumer Owner], RPT_CCC_PROJECTS_DIM.SPONSOR AS Sponsor, RPT_CCC_PROJECTS_DIM.PROJECT_MANAGER AS [Project Manager], RPT_CCC_PROJECTS_DIM.PROJECT_START_DATE AS [Project Start Date], RPT_CCC_PROJECTS_DIM.CUSTOMER_REQUIRED_BY_DATE AS [Customer Required By Date]
FROM RPT_CCC_PROJECTS_DIM INNER JOIN RPT_CCC_TASK_DIM ON RPT_CCC_PROJECTS_DIM.PROJECT_CODE = RPT_CCC_TASK_DIM.PROJECT_CODE
WHERE (((RPT_CCC_PROJECTS_DIM.PROJECT_CODE)<>"PM0006KU") AND ((RPT_CCC_TASK_DIM.EXT_TASK_ID)="SG1" Or (RPT_CCC_TASK_DIM.EXT_TASK_ID)="SG2" Or (RPT_CCC_TASK_DIM.EXT_TASK_ID)="SG3" Or (RPT_CCC_TASK_DIM.EXT_TASK_ID)="3SG1" Or (RPT_CCC_TASK_DIM.EXT_TASK_ID)="3SG2" Or (RPT_CCC_TASK_DIM.EXT_TASK_ID)="3SG3") AND ((RPT_CCC_TASK_DIM.STATUS)=0) AND ((RPT_CCC_PROJECTS_DIM.PRJ_APPROVAL_STATUS)="Approved")) OR (((RPT_CCC_TASK_DIM.EXT_TASK_ID)="SG" Or (RPT_CCC_TASK_DIM.EXT_TASK_ID)="3SG") AND ((RPT_CCC_TASK_DIM.STATUS)=1) AND ((RPT_CCC_PROJECTS_DIM.PRJ_APPROVAL_STATUS)="Approved"))
ORDER BY RPT_CCC_PROJECTS_DIM.PROJECT_CODE;

Thank You

 

[ByteMyzer]

Your question is not clear. What exactly are you asking? Is one of the fields in your SQL statement a MEMO field? Are you worried about it being truncated? Or are you thinking of your entire SQL statement as your expression, and if that's the case, are you worried about your SQL statement being truncated? Please clarify.

Let's start with re-formatting the query for better readability:

SELECT DISTINCT R1.PROJECT_CODE AS [Clarity Ref],
    R1.PARENTPROJECT,
    R1.BU_PRIORITY AS [Priority Score],
    R1.BU_UNIQUE_REFERENCE AS [CPN Ref],
    R1.PROJECT_TITLE AS [Project Title],
    R1.INITIATOR AS [Consumer Owner],
    R1.SPONSOR AS Sponsor,
    R1.PROJECT_MANAGER AS [Project Manager],
    R1.PROJECT_START_DATE AS [Project Start Date],
    R1.CUSTOMER_REQUIRED_BY_DATE AS [Customer Required By Date]
FROM RPT_CCC_PROJECTS_DIM AS R1
INNER JOIN RPT_CCC_TASK_DIM AS R2 ON R1.PROJECT_CODE = R2.PROJECT_CODE

WHERE R1.PRJ_APPROVAL_STATUS="Approved" AND

(
  (
    R1.PROJECT_CODE<>"PM0006KU" AND
    R2.EXT_TASK_ID IN ("SG1", "SG2", "SG3", "3SG1", "3SG2", "3SG3") AND
    R2.STATUS=0
  )

  OR

  (
    R2.EXT_TASK_ID IN ("SG", "3SG") AND
    R2.STATUS=1
  )
)

ORDER BY R1.PROJECT_CODE;

So, what exactly is your question?

 

[purceld2]

ByteMyzer thank you for your reply sorry if I have not made my question clear.

Basically my question is that my sql statement is longer than 255 charactors long which is causing the memo feilds to truncate. also using DISTINCT causes truncation. can you explain to me some of the methods used to get round these problems. I see that you have reduced my SQL code by assigning a code to it.

when access says 255 charactors does that inculde table and field names because if you use the Access grid to constrct your SQL you cannot control this.

Sorry if this is not clear I am new to access and do not know the terms.

Thank you very much for your help.

Regards

Desmond

 

[ByteMyzer]

Which of the fields in your query are Memo fields?

 

[purceld2]

I have taken them out as they are being truncated because of the distinct key word in the SQL. I had hoped to add them back in after retriving the data. Is this the best approach?

 

[ByteMyzer]

Are the Memo fields in table RPT_CCC_PROJECTS_DIM or table RPT_CCC_TASK_DIM?

 

[purceld2]

In the RPT_CCC_PROJECTS_DIM table

 

[ByteMyzer]

In that case, you might try the following (substitute highlighted text with the actual field name):

SELECT R1.PROJECT_CODE AS [Clarity Ref],
    R1.PARENTPROJECT,
    R1.BU_PRIORITY AS [Priority Score],
    R1.BU_UNIQUE_REFERENCE AS [CPN Ref],
    R1.PROJECT_TITLE AS [Project Title],
    R1.INITIATOR AS [Consumer Owner],
    R1.SPONSOR AS Sponsor,
    R1.PROJECT_MANAGER AS [Project Manager],
    R1.PROJECT_START_DATE AS [Project Start Date],
    R1.CUSTOMER_REQUIRED_BY_DATE AS [Customer Required By Date],
    R1.[[b][i]MyMemoField[/i][/b]]
FROM RPT_CCC_PROJECTS_DIM AS R1

WHERE R1.PRJ_APPROVAL_STATUS="Approved"
AND EXISTS (
    SELECT R2.PROJECT_CODE
    FROM RPT_CCC_TASK_DIM AS R2
    WHERE R2.PROJECT_CODE = R1.PROJECT_CODE
    AND ((
          R1.PROJECT_CODE<>"PM0006KU" AND
          R2.EXT_TASK_ID IN ("SG1", "SG2", "SG3", "3SG1", "3SG2", "3SG3") AND
          R2.STATUS=0
         ) OR
         (
          R2.EXT_TASK_ID IN ("SG", "3SG") AND
          R2.STATUS=1
       )))

ORDER BY R1.PROJECT_CODE;

 

[purceld2]

ByteMyzer
Thank you for your reply. how can you tell if your SQL expression exceeds the 255 characters. does it inculde the table name/ fields etc.

what is best practice when you need to use DISTINT key word and have Memo fields in the table.

thanks agian for all your help

Regards

Desmond

 

[ByteMyzer]

For starters, how about applying my recommended syntax (with the proper memo field name) and testing it to let me know if it worked for you? I think you will be pleasantly surprised.