Need Help Troubleshooting a Database Setup

[Russellh]

Attached is a copy of a small database that I am trying to use for logging phone calls.
There is a report titled "Issues Details" that produces a nice printable version of the tickets.
Rather than printing every ticked that exist each time you run the report I want to be able to enter a date range.
So I created a new query "Issues Date Range" which allows the user to enter a beginning date and an ending date for the records they want to pull.
I then copied and pasted the original report called "Issues Details" and renamed the new one "Issues Date Range".
I then changed the record source to point to the query "Issues Date Range" which is the new query.


The problem is that the comments show an "error" instead of the comments.

The comment field is setup as a memo and somehow the form allows you to open back up the record and add a new comments. When you run the original report it shows all the comments with thier time stamp.
However I get the error message when I run the copy I tried to make.

Can someone troubleshoot it for me?

[CJ_London]

the comment history you are referring to is a property only available directly through the table, it is not available or passed on through a query. The property is called append only and can be seen in the table design at the bottom of the memo field properties (you'll need to scroll). When Append Only is set to yes, all changes are appended to the memo field rather than updating them. google 'access memo append only' to find out more.

To prove this, modify the recordsource of your issue details report to "select * from issues" and you will have the same problem.

It looks like all you need to do is bind your comment control to the comments field rather than the '=ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0))' you have at the moment. You will lose the version information, but perhaps that doesn't matter. If it does there is a workaround - not very efficient but will keep you going for now.

1. Create a new report called say memosubreport
2. add 'issues' to the recordsource (not a query)
3. remove page header/footer and just have the memo field with your =columnHistory...controlsource
4. in your issues date range report design view, drag this subreport onto the report and set linkchild/master properties to ID
5. you will probably need to do things around cangrow/shrink etc

Otherwise regret there is no easy workaround as such other than to do it the 'normal' way - that is to have a separate table for memo fields with a family key to bind it back to the issues table - then you have one record for each entry and use a subform/report to display them. This has certain other benefits such as you can have additional fields to record who entered the post, what the next action is, by who and by when, etc

[Russellh]

the comment history you are referring to is a property only available directly through the table, it is not available or passed on through a query. The property is called append only and can be seen in the table design at the bottom of the memo field properties (you'll need to scroll). When Append Only is set to yes, all changes are appended to the memo field rather than updating them. google 'access memo append only' to find out more.

To prove this, modify the recordsource of your issue details report to "select * from issues" and you will have the same problem.

It looks like all you need to do is bind your comment control to the comments field rather than the '=ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0))' you have at the moment. You will lose the version information, but perhaps that doesn't matter. If it does there is a workaround - not very efficient but will keep you going for now.

1. Create a new report called say memosubreport
2. add 'issues' to the recordsource (not a query)
3. remove page header/footer and just have the memo field with your =columnHistory...controlsource
4. in your issues date range report design view, drag this subreport onto the report and set linkchild/master properties to ID
5. you will probably need to do things around cangrow/shrink etc

Otherwise regret there is no easy workaround as such other than to do it the 'normal' way - that is to have a separate table for memo fields with a family key to bind it back to the issues table - then you have one record for each entry and use a subform/report to display them. This has certain other benefits such as you can have additional fields to record who entered the post, what the next action is, by who and by when, etc

Is there a way to modify the report that works correctly to ask for a date range? If so that would be the easiest I think?

[CJ_London]

Is there a way to modify the report that works correctly to ask for a date range? If so that would be the easiest I think?

not as such

rather than opening the report 'manually' from the navigation window, have a form with controls for the dates and a button to open the report

the code behind the button will use docmd.openreport which has a criteria parameter - so use this to open the original report and build the criteria from the two controls

[Russellh]

I'm going to attempt created the extra form. How do I create two boxes where the user can select the two dates for the range? Are they text boxes? I am doing this from the Form Design view.

[orange]

Try Martin Green's site.

Good luck.

[Russellh]

OK I created the form. I added two text boxes and set formats to small date which is same as date on the report.
I created a button with used the event wizard for the action when its clicked.
[Reports]![Issue Details]![Opened Date]>=[Text3] And [Reports]![Issue Details]![Opened Date]<=[Text5] is the conditions box for the command to open the report.

When I enter 2 dates and then click the button it asks me to enter the text3 and enter text 5. How do I fix it so it uses what was entered in the 2 boxes?

Also it returns no results. Even though I know I have records for today.

[Russellh]

When I clear the condition out it simply opens the report and lists all the records so that leaves me to believe it has something to do with the condition.

**UPDATE** rather than putting the where condition on the report I put it on the table that the report pulls from and that part is working.

Now I just need to know how to get it to pull from what is in the two unbound fields for beginning and end date.

[CJ_London]

You need to learn about how to build strings, how date types, text types and number types of fields are treated.
the criteria for docmd.openreport would be

"[Opened Date] >=#" & format(Text3,"mm/dd/yyyy") & "# AND [Opened Date] <=#" & format(Text5,"mm/dd/yyyy") & "#"

**UPDATE** rather than putting the where condition on the report I put it on the table that the report pulls from and that part is working.

I thought you said that when the query was filtered, you could not access the memo properties

[Russellh]

You need to learn about how to build strings, how date types, text types and number types of fields are treated.
the criteria for docmd.openreport would be

"[Opened Date] >=#" & format(Text3,"mm/dd/yyyy") & "# AND [Opened Date] <=#" & format(Text5,"mm/dd/yyyy") & "#"

I thought you said that when the query was filtered, you could not access the memo properties

Ok. So to get my date range I tried 2 paths. 1st path was to create a query (to pull out the date range) and then copy the report but have it point to the query. This failed to produce the correct memo field. 2nd path (due to recommendation from forum) was to create a dialog form and then have it open the report filtering for those dates. This method I got to work but I was having trouble getting the fields to save as variables and use that rather than asking the user to input the date once they hit the open report button.

***UPDATE*** With some help I was able to get my 1st method to work. So I am able to open the copied report (named "Issue Reports Date Range") which is based on the query and it now reports the memo with version history correctly.
I will eventually change this to a comment button that just appends a large text box with a timestamp and the new comment.

This has turned out to be a fun project and I have a pretty good Database to capture call records with timestamped comments. Thanks for all the help and suggestions!!!