Trying to construct a query in Access 2007. One of the columns has the data type "Memo". Sometimes the latest version is returned and for others the display is blank. Only if I right-click and select "Show column history" on each row, can I actually see the full set of data in this column. Is there a way through the query designer or SQL that this data can be retrieved in it's entirety?
A:
Found the answer by defining a VBA function which returns the column history as a string. Then this function is used in the query "Field" value. Right click on "Field" cell in query builder and select "Build" from the right click menu. Here you can find the VBA function. The expression builder will look something like this:
Expr1: GetHistory([ID])
Here is the VBA function:
Public Function GetHistory(rowID As Integer) As String
On Error Resume Next
Dim sHistory As String
sHistory = Application.ColumnHistory("Table Name", "Column Name", "ID=" + CStr(rowID))
GetHistory = sHistory
End Function
Helpful reading:
bob.faist
2010-10-14 17:52:09
Another helpful link: http://msdn.microsoft.com/en-us/library/bb242869%28office.12%29.aspx
bob.faist
2010-10-14 17:53:50
I believe that these new append-only memo fields (new in A2007) are a mistake -- it's denormalized data, and each of the individual edits should be a record in a separate table. Also, keep in mind that these (along with multi-value fields and the attachment field, all of which work on the principle of hiding a N:N structure behind the UI) were introduced into Access for compatibility with Sharepoint. Outside of that context, I think I'd avoid them.
David-W-Fenton
2010-10-16 17:13:28
The Access Database that required a query based on this "Memo" field was created through SharePoint. I agree they are worth avoiding but this was a pre-existing database and the request was driven by management to get this data on a Access Report. Fun times.
bob.faist
2010-10-18 03:28:32