Searching column history

[feenixfire]

Hi all,


I've got an inventory DB in access 2010 where i track our assets. In my asset table there's a "comments" field (memo type) with the comment history. I got this from the northwind assets db or the access built in inventory db, don't recall which one. Anyway, the comment history has been awesome but i need to search the history and i can't. For example 1 asset in the history, if i view the history, i can see was first assigned to jsmith but when i search for "smith" it doesn't appear because it's in the comment history. Any suggestions would be greatly appreciated!

[June7]

How are you doing this search - with the Access shortcut menu, in a query, or in VBA code? I just tested searching and filtering for a string within a memo field by using the shortcut menu and a query and it worked.

[feenixfire]

I've tried a query and ctrl F. I'd like to avoid code. So here's my scenario, maybe it'll clear things up. I have say 10 records. In the memo field i named "comments" i have jsmith for the last comment. However, in another record, i had a comment months back named jsmith which does not show up in the table because i've put in new comments since then. If i right click on the field, and view column history, i can see that old comment and when i entered it. When i do a query or CTRL F, for the word "smith", all that will show is that first record with the recent comment. Searching/querying does not appear to search the history of the memo field and from what i can tell access hides where that data is.

How are you doing this search - with the Access shortcut menu, in a query, or in VBA code? I just tested searching and filtering for a string within a memo field by using the shortcut menu and a query and it worked.

[June7]

Still confused. What do you mean by 'column history' - all records? Why would a more recent record prevent an older record from showing?

I am hoping this is not a multi-value field.

Do you want to make project available for analysis, or at least just this table?

You might find this recent thread of interest https://www.accessforums.net/queries...ory-15580.html

[rpeare]

If you're searching a memo field you'll want to search with LIKE

like * & [Enter Search Text] & *

this should find anything that has the text string you enter if you search on the memo field.

EDIT: I don't know what type of results you'll get with ctrl-f this, I'm talking about building a query that will perform the search, not using the innate windows search function.

[feenixfire]

OK i know it's confusing, i've gone round and round with my SQL buddy on this, so hopefully this attachment uploads. I made a table, 2 fields, username and comments. The comments field is a memo type, append only as yes. So, open the table and look at bob's comments. It states "wait I meant he's really mean" but if you right click that comment, then click show column history, you'll see that a minute earlier i put a comment of "bob is a good guy". It tracks what i've entered and when i changed it. No matter if i use crappy ctrl+F (completely agree with you on that one BTW) or LIKE in an SQL query, if i search for the word "good" that i know is on a previous comment, it will not pull that record. This historical data has got to reside somewhere and unfortunately this is an issue i wasn't aware of for quite some time so i'm looking at recreating all comments one by one. Hopefully that clears up the issue and I'm hopefull you gurus will have an idea on what i can do to fix it. Thanks for the assist!!!

[June7]

Well that is a column property I never explored before. Access is saving the previous entries somewhere. I set project so I can view hidden system tables. None of them show this data.

I did find one reference that stated this data is stored in a multi-value type field. But it doesn't act like a multi-value Text datatype field.

EDIT: VBA can retrieve, check page 60 of this pdf http://www.msoffice.us/Access/PDF/Us...ess%20Data.pdf
and this article http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

I typed this in the VBA immediate window:
?Application.ColumnHistory("test","Comments","ID=2 ")
and the output was:
[Version: 8/4/2011 3:37:35 PM ] comments
[Version: 8/4/2011 3:37:44 PM ] bob is a good guy
[Version: 8/4/2011 3:38:14 PM ] wait I meant he's really mean

[rpeare]

You can view your history in a query with this kind of code:

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

You can do searches for *good* in the comment history field as well so it may do what you want.

It will show the most recent comments and the comment history. If you want to undo the add only property of the memo field you would have to write some code to read all the history comments and append them as one block of text in your 'current record' then change the field from add only to a normal memo field. Just make sure you are not working on your original data in case something goes wrong

[feenixfire]

Dude you guys rock thank you so much. I tried the SQL and the VB, both worked great and exactly what I wanted. Thank you SO MUCH!