ColumnHistory Use in Queries

[gden]

Hi,

I am an inexperienced ACCESS user. I am trying to create a query that pulls the column history, including the date/time stamp that ACCESS applies to the historical entries in the Comments column in the Issues table in the issues database in ACCESS 2007's Featured Online Templates.

I tried just leaving the criteria blank for the Comments column in query design and then selecting only the record ID; however, the query only returns the most recent entry in the Comments column. I also tried using the control syntax that ACCESS 2007 uses in the Issue Details form: =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0)); however, when I execute the query, it prompts me for the "Record Source" to which I enter "Issues", and then the query result set is blank.

Is there any way to create a query that, when executed, returns the complete column history for the Comments column, or is the ColumnHIstory function limited to forms and reports? Thank you.

 

[stopher]

The ColumnHistory function will work fine in queries (Access 2007 onwards I think).

You were on the right track. You need to replace the [RecordSource] in the expression with "Issues"

So you query might look like this:

SELECT ID, Comments, ColumnHistory("issues","Comments","[ID]=" & Nz([ID],0)) AS CommentsHistory
FROM Issues

hth
Chris

 

[stopher]

I forgot to mention that when you run the query, the query will probably default to show only a single line. So if you want to see all the results, expand the row height (like you would in excel) to see more lines. If your creating the query as the source for a report then don't worry about it. Just make sure your report field can show enough lines.

Chris

 

[stopher]

And welcome to the forum

 

[DCrake]

Does Columnhistory only work on memo fields or are there other control types it can be used on. And what advantage is gained on memo fields?

 

[stopher]

It would seem so:
http://msdn.microsoft.com/en-us/library/bb242869(office.12).aspx

Only a memo field has the Append Only option which is needed to be set for the history to be stored.

As to the advantage of a memo field... no idea character capacity?

Chris

 

[stopher]

Looks like you can set a memo field in 2007 to be rich text. I guess that's pretty useful.

 

[vbaInet]

Looks like you can set a memo field in 2007 to be rich text. I guess that's pretty useful.

Oh yes Chris

 

[DCrake]

Yes I found that out, but the only problem is that you don't get the toolbox when used in a subform.

 

[stopher]

Yes I found that out, but the only problem is that you don't get the toolbox when used in a subform.

Allen Browne's page pointed to a hotfix here.

Chris

 

[gden]

Stopher,

Thank you very much, the code worked great. I've spent a lot of hours on this one and wasn't getting anywhere quickly. I didn't think to set the alias. Also, thank you for the welcome.

Based on my limited experience, the major benefit of the memo field seems to be character capacity. I've used memo fields to capture long strings where I needed to document discussion logs. I was pretty excited when ACCESS 2007 added the ColumnHistory function...it made capturing the discussion logs a lot easier.

Thank you,
gden.

 

[Galaxiom]

I have never used them but it seems one drawback with memo fields is they are more prone to corruption. Some developers put the memo field in a one to one related table to insulate the rest of the data from the potential damage.

I expect the potential for trouble escalates when the field exceeds 2000 characters. At this point the rest of the data is held outside the field in a BLOB.

I recall a post here a few months ago where the user reported a problem with a memo field only showing the last 2000 characters. If I remember correctly they were concatenating data to the field using VBA on a regular basis. I expect they are best behaved when a record is entered and left alone. Probably better to add multiple records than concatenating to memo fields in existing records.

Note that joins and (I think) searches only deal with the first 255 characters of the memo fields. (Not that anyone in their right mind would join on a Memo field.)

Perhaps I am dreaming but I think I saw somewhere that appends only carry the 255 characters for Memo fields.

 

[ComptechRx]

Does anyone know if it's possible to print the full ColumnHistory with dates and all entries in a report? I tried adjusting the fields to show more lines but that didn't do it. I currently have the report pulling the ColumnHistory from a form. TIA

 

[stopher]

I think you need to write a function to get the history. Something like this:

Public Function ColumnHist(ID As Integer) As String
    
   
    ColumnHist = Application.ColumnHistory("myTable", "myMemoField", "ID=" & ID)


End Function

hth
Chris