How to prevent MS Access truncation or mangling of expressions over 255 characters in length

There’s a weakness in Access/JET that if a expression based on a memo field outputs a string longer than 255 characters, the result gets garbled when used in a recordset. This does not affect normal queries, forms or reports, unless they rely on a recordset (e.g. the expression uses a custom funciton that uses a recordset, or the whole expression is processed in a recordset).

I have tested this in Access 2010 and the problem remains.

One solution is to force Access to think of the output as a “memo” field by using a UNION onto a table with the problem column as a “MEMO” field in the table design (generally using an alias to achieve this). Here is an example to prevent truncation of a bibliographic reference in HBSMR, where we are pretending that the Mon.Descr field (MEMO) is the same as the calculated Reference from the second part (which is doing the actual work). Note that the first part says WHERE 1 = 0 so that it adds zero records to the output.

SELECT MonUID, Prefref as SourceUID, prefref as [Number], Descr as Reference, easting as SourceNumSort

FROM Mon WHERE 1 = 0

UNION ALL

SELECT

MonSourceLink.MonUID, SourceTbl.SourceUID, IIf(IsNull([SourceNo]),"---",

IIf([SourceNo]<>GetNumeric([SourceNo]),[SourceNo],"<" & [sourceNo] & ">")) AS [Number],

Nz([SourceTypeLUT]![Desc],"(No record type)") &

": " & (IIf(IsNull([originator]),"",[originator] & ". ") &

IIf(IsNull([origdate]),"",[origdate] & ". ") &

IIf(([SourceTypeLUT].[SourceType]="PAR"),"'" & [Title] & "' " & "in " & [Var1] &

", pp " & [Var2],(IIf(IsNull([title]),"",[title] & ". ") &

IIf(IsNull([var1]),"",[var1] & ". ") & IIf(IsNull([var2]),"",[var2] & ". ") &

IIf(IsNull([var3]),"",[var3] & ". ")) & [Ref] & ". ")) AS Reference,

GetNumeric([SourceNo]) AS SourceNumSort 

FROM (MonSourceLink LEFT JOIN SourceTbl ON MonSourceLink.SourceUID = SourceTbl.SourceUID)

LEFT JOIN SourceTypeLUT ON SourceTbl.SourceType = SourceTypeLUT.SourceType 

WHERE MonSourceLink.MonUID="MWI20580";

However, this technique always produces a read-only recordset, so is only useful in some curcumstances.

Comments