Link to home
Start Free TrialLog in
Avatar of COSMTARFCU
COSMTARFCUFlag for United States of America

asked on

Access 2007 Report with date range in column history

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!
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

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


Avatar of COSMTARFCU

ASKER

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.
 
<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
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
?

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


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
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
:-(
Nobody says "Thanks" anymore...
Avatar of tty5
tty5

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