Access 2007 Report with date range in column history

COSMTARFCU
COSMTARFCU used Ask the Experts™
on
I'm trying to create a report in Access 2007 that will show all the comments that occured in the last 7 days.  The comments field is a memo with the columnhistory function set, and what I would like is to only pull comments in the columnhistory in the past 7 days.

I tried doing this, but just get an error:  ("#Error" shows up in the report itself)
=ColumnHistory([RecordSource],"Comments","[ID]=" & DateDiff("d",[Comments],Date())<=7)

I also tried =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0) Where (DateDiff("d",[Comments],Date())<=7) but it yelled at me that the syntax is incorrect.

I know very little VB (as you can tell) so I would appreciate any guidance.

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
In the most basic sense, if you need data from the last 7 days only you can use a query like this:

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate Between Date() And Date()-7

Since you posted no information on this mysterious "columnhistory" function, (or even how your data/report is structured/designed) I really can't comment on how to incorporate it into a solution.

Perhaps if you posted the specific details of your report and its source data, this would make things clearer.
(Preferably with a sample database)

;-)

JeffCoachman


Author

Commented:
ColumnHistory is actually a method included in access 2007 (sorry, didn't mean function...meant method, don't know if it makes a difference)
The DB I am working with is the "Issues" template for MS Access 2007.  The table is called Issues, with a field called "Comments".  Comments is a memo field with the append only set to yes.  (MS info:  http://msdn.microsoft.com/en-us/library/bb242869.aspx)
The DB can't be uploaded because it isn't in the list of extensions that I can put up.
 
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<The DB can't be uploaded because it isn't in the list of extensions that I can put up.>
If this is an Access 2007 database, simply append  ".mdb" to the filename, and it will upload here with no problem.

For example rename:
   YourDatabase.accdb
...to
   YourDatabase.accdb.mdb
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Here's the DB.  I am trying to copy the Issue Details report and change the comment section from =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0)), which is returning ALL comments in the history to something that will only return the comments posted in the last 7 days.
I think I need to remove the ID= & Nz([ID],0 section and replace it with the datediff, but everything I try gives me a invalid syntax.
Thanks!

Copy-of-ORIGINAL-Issues.accdb.mdb
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
?

Your Issue table only has 1 complete record and 1 incomplete record.
Neither record having a comment?

...So it is hard to see the problem you are describing.

Are you sure you are using the ColumnHistory Correctly?

If the report lists each Issue then only that issues comments will be visible for each record.

Are you trying to show the 7 day comment history for *All* records in *each* record?

I'm confused.


To make this simple, can you post a sample DB that has a fair amount of records that are representative of your actual data.
This way I can clearly see the problem you are describing.
Then show me "Graphically" what you need the output to be, based on the sample data.

Thanks

JeffCoachman


Author

Commented:
Ok...  In the report screenshot.bmp image,  you can see the issue details report shows all comments put in.  I'd like it to be limited to ONLY comments made within a selected time frame (7 days).  As the individual records grow, you can imagine that the report gets VERY long and is difficult to read, so we'd like to limit it to a weekly report.
I also put in a few more examples with comments so you can see what I am talking about.

report-screenshot.bmp
Copy-of-ORIGINAL-Issues.accdb.mdb
MIS Liason
Most Valuable Expert 2012
Commented:
OK, now I see.

While what you are asking can certainly be done, Using "ColumnHistory" is not the best way to track this data.


The "Issues" (no pun intended) with using this Method (no pun intended):

- This ColumnHistory Data is a history of "Deleted" values.
- This functionality is specific to Access 2007 and is not readily transferable to other, more robust Database platforms.
- (Even if this DB needed to be run in Access 2003 this functionality would produce errors)
- Also, this info is only available in Memo fields, so any data that you would need this for would have to be converted to this inefficient and sometimes problematic datatype.
(http://allenbrowne.com/ser-63.html)
- Also you must always remember to set the AppendOnly Property to True.
- HTML formatting vs Rich text Formatting issues, complicate this further.
- It is not even clear where this data is stored, or what it's date limits are.
- Because you are actually looking at "Deleted" data, it may not be easy to manipulate this history in ways that may be requested.
For example:
Only comments on Mondays
Comments sorted in Descending order
Comment over 50 characters long
Character containing the word. "Moose"
- AFAICT the date on this data is the "Change/Delete" date, so if a person "Piddled" around in this field, it would record all of these possibly meaningless "piddles"
;-)
(Also suppose you wanted to keep the date/time but change the text, ...or vice versa)
- Database corruption issues sometimes occur in Access  Databases.  The most common fix is to create a blank new database and import all the objects.  This will effectively *Delete* this history! (It is still unknown under what other circumstances this data might be cleared.)
...etc
(Basically, for me, it just seems odd to have to "Delete" a value, in order for it to *appear* somewhere else?
;-)

IMHO, all of these factors taken together make this type of functionality unpredictable at best, and undependable at worst.

The standard way of doing this is to have a Comment(History) Table.
Then:
1. None of the above issues are even relevant.
2. This becomes ridiculously easy and full-proof to: create, explain, document, and troubleshoot (Instead of incredibly "dodgy")
;-)

Here is a sample
Note the new form that allows for entering and viewing of an Issue and *All* of it's associated comments.
The source query for the report is simply the same as I mentioned earlier:
SELECT ...
FROM ...
WHERE tblComments.CommentDateTime Between Date() And Date()-7;

Note that the Report shows each Issue with only comments from the last 7 days.

I am sure you can adapt the concepts shown in the sample to work in your database.
Have fun!
;-)

JeffCoachman
Access-EEQ24897677-Access2007Col.mdb
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
:-(
Nobody says "Thanks" anymore...

Commented:
Thanks Jeff,
Your very clear break down of why not to use the "new" =ColumnHistory feature helped me in a new design!  I found this thread while trying to get my head around why I might use this new Access 2007 feature.  Thanks for showing a more traditional way to do it and the "why it's bette" logic

Nancy

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial