How to retrieve column history from a memo field in Access 2007?

Question

An attempt was made to build a query in Access 2007. One of the columns has a data type of "Memo". Sometimes the latest version is returned, but for others it’s empty. Only if I right-click and select "Show column history" in each row, can I see the full data set in this column. Is there a way to design a query designer or SQL so that this data can be retrieved in its entirety?

Answer 1

Found the answer by specifying a VBA function that returns the history of columns as a string. This function is then used in the Field request. Right-click the Field cell in the Query Builder and select Create from the right-click menu. Here you can find the VBA function. The expression constructor 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

Useful reading:

• VBA function in request example