Memo truncates to 255 on report (not because of DISTINCT)

[vbaDev]

Hi. I did a search on this issue and know that there are a few reasons why Access truncates Memo fields.

In my case I have a MEMO field with a default value "[Enter Description Here]", which the user can replace with text.

Obviously I don't want the report to show the default placeholder text in brackets. So in my query I have:

Scope_Desc: IIf(Left([scope_description],1)='[','',[Scope_Description])

so that default bracketed text displays as blank, but valid description appears.

Obviously, Access truncates this to 255 characters, because the field is processed.

Here's the full SQL:

SELECT tblAssessments.OccupantID, tblAssessments.AssessmentID, tblAssessments.Date, tblAssessments.Status, tblDepartments.Department, tblWorkstations.Workstation, IIf(Left([scope_description],1)='[','',[Scope_Description]) AS Scope_Desc, IIf(Left([applicability],1)='[','',[applicability]) AS Appl
FROM tblDepartments RIGHT JOIN (tblWorkstations RIGHT JOIN tblAssessments ON tblWorkstations.WSID = tblAssessments.WSID) ON tblDepartments.DeptID = tblAssessments.DepartmentID
WHERE (((tblAssessments.AssessmentID)=[Forms]![frmMain]![sbfmAssessments].[Form]![AssessmentID]))

What would be the best workaround?

P.S. I tried just using "Scope_Description" without processing and it works (but displays the unwanted bracketed defaults).

Thanks!

 

[Alisa]

You could use the unprocessed description, and then in the format event of the detail section, you could write Me.Scope_Desc.Visible=Not Me.Scope_Desc="Enter Description Here". Then, if the description does equal Enter Description Here, your code will make it invisible.