Get LastDate from Continuous form into Report

[June7]

What did I miss? I did not know the form had an issue, only the report.

[Leen]

Dear June7,

Thank you so much for your response!

I can see the reasoning behind subreports; however, I believe that even subreports would not solve the problem w/ the continuous form.
Nonetheless, I am very grateful for your input and help.

Again, thank you very much.

Leen

Hi June7,

I feel I am getting closer to a solution, but still definitely need your help.

What I did: I placed 4 text boxes (VisitDate, Item1, etc.) in the Footer of the MainFrm, made them invisible.

Placed this code in them:


=DLast("VisitDate","Visit","PatientID=" & [PatientID])

=DLast("Item1","Visit","PatientID=" & [PatientID])



In the Reports I placed the following code:

=[Forms]![MainFrm]![VisitDate]
=[Forms]![MainFrm]![Item1]

and this works perfectly well with a Report for one patient.

Now I need to distinguish the code for when I use the parameter to print out patients alphabetically, so it "reads" each PatientID per page.

I hope you understand what I am trying to say.

Any ideas?

Thanks again!

[June7]

Why are the controls hidden? What purpose do they serve?

Expressions referencing form controls will only grab data from record that has focus on the form when the report first opens.

[Leen]

Why are the controls hidden? What purpose do they serve?

Expressions referencing form controls will only grab data from record that has focus on the form when the report first opens.

The idea is to get data from a continuous form into a report, which seem to be almost impossible, given the trouble one can get into. Someone advised this, because it is also a much more direct route in stead of letting the report calculate. (This field is not necessary in the main form). This definitely slows the report down.

As said before, the one single report page shows the correct LastVisitDate. However, when using the parameter to print a group of patients, e.g. the "A's" the first report page shows the correct last visit date, but the following pages are going bananas, regarding this date.

This goes big time over my head...

Thanks for your help!

[June7]

Have you considered a normal SELECT (no grouping) query as report RecordSource then use report Sorting & Grouping feature with aggregate expressions in textboxes in report and group header/footer sections?

[Leen]

Have you considered a normal SELECT (no grouping) query as report RecordSource then use report Sorting & Grouping feature with aggregate expressions in textboxes in report and group header/footer sections?

Dear June7,

There is a reason why the "Group by" setting is chosen, because of the 4 memo fields losing text otherwise.
Secondly, could you give an example how to do that?

[June7]

I avoid memo fields as much as possible. I put more than 255 characters in memo field and the GROUP BY query also loses text. So how does this query resolve issue of memo field in aggregate expression losing text? In query or report design, memo fields don't aggregate.

I did a quick test of report based on non-grouping query. Using report Sorting & Grouping, created a group on PatientID. Textboxes in that group header bound to PatientID and LastName. No data in Detail section. One record for each patient displays.

I also never use dynamic parameters in queries. I input filter criteria into UNBOUND control (usually a combobox) and reference that control to open report with filter:

DoCmd.OpenReport "report name", , , "PatientID=" & Me.comboboxname

[Leen]

I avoid memo fields as much as possible. I put more than 255 characters in memo field and the GROUP BY query also loses text. So how does this query resolve issue of memo field in aggregate expression losing text? In query or report design, memo fields don't aggregate.

I did a quick test of report based on non-grouping query. Using report Sorting & Grouping, created a group on PatientID. Textboxes in that group header bound to PatientID and LastName. No data in Detail section. One record for each patient displays.

I also never use dynamic parameters in queries. I input filter criteria into UNBOUND control (usually a combobox) and reference that control to open report with filter:

DoCmd.OpenReport "report name", , , "PatientID=" & Me.comboboxname

After reading your post +/- 6 times, I get the idea (a bit).

One advised to set the memo fields all to "First" in GROUP BY, so they won't be truncated (I even believe it came from a MS Support site).

In the past I have looked at the Report Sorting and Grouping, but never worked with it. As mentioned before, I learn mostly by example. I hear the question, see the answer from an expert, and get it. That's pretty much how I understand things.
I myself prefer also a combobox (there are several in my DB), but am not sure how to relate that to finding more than one patient (e.g. to print out alphabetically).

You have my DB (in my full DB there are over 1500 Pts); could you use that to give me an idea?

[June7]

Did you do any testing of memo field with more than 255 characters?

I am not sure what to tell you. I gave a description of report and using combobox to input filter criteria.

http://www.datapigtechnologies.com/f...mtoreport.html uses dynamic parameterized query

http://www.allenbrowne.com/ser-62.html

If you want to retrieve only the latest (or earliest) record for each patient, here is one method http://allenbrowne.com/subquery-01.html#TopN

[Leen]

Did you do any testing of memo field with more than 255 characters?

I am not sure what to tell you. I gave a description of report and using combobox to input filter criteria.

http://www.datapigtechnologies.com/f...mtoreport.html uses dynamic parameterized query

http://www.allenbrowne.com/ser-62.html

If you want to retrieve only the latest (or earliest) record for each patient, here is one method http://allenbrowne.com/subquery-01.html#TopN

Testing Memo Fields: Yes, I did. It caught my attention b/c one of the memo fields can contain thousands of characters, and was cut off at the 255 character count. When using the GROUP BY, and setting the memo fields to "First", I got the whole memo text, with no truncation at all.

About the rest of my last response: I think I understand each item you're talking about, but have a hard time putting it together in my mind, or seeing the whole picture...

[June7]

Correction, I was looking at wrong patient. Now I see the full memo field content in the GROUP BY query.

Sorry, I have to give up on your queries. Even with all the aggregate calcs removed, the query without dynamic parameter filter won't retrieve records unless I deactivate the GROUP BY parameter.

[Leen]

Correction, I was looking at wrong patient. Now I see the full memo field content in the GROUP BY query.

Sorry, I have to give up on your queries. Even with all the aggregate calcs removed, the query without dynamic parameter filter won't retrieve records unless I deactivate the GROUP BY parameter.

That is strange, to say the least!
When I have the MainFrm open and double click on "PatientSelectCharRpt", while all GROUP BY's are in place, the outcome is great, EXCEPT regarding the LastVisitDate on the Report; that is fine with the first record, but the second and third, etc., gives strange looking dates. Whenever I remove the LastVisitDate from the Report, it produces all requested patient reports.

[June7]

Okay, this fixes the date textbox in report. Change RunningSum property to No.


But why won't the query or report accept criteria of F or S - just says there are no records.

And the MxN textbox on report is not growing to display full content. Would you really want it to?

[Leen]

Okay, this fixes the date textbox in report. Change RunningSum property to No.


But why won't the query or report accept criteria of F or S - just says there are no records.

And the MxN textbox on report is not growing to display full content. Would you really want it to?

Maybe there are no records starting with that letter? I made up a few fake records.
I did change the RunningSum to No, but with no results.

It does become more clear to me though. Two things I can mention here:

1) When double clicking the parameter query, while the MainFrm is open, it always gives the date of the form at present.
2) When clicking without the form being open, it gives the familiar "Name?

I hope this helps you to find a way?

[June7]

There are records for Fonda and Smith but the aggregate query won't show them.

Changing RunningSum property should fix the date display. Does for me.

Not seeing #Name! error anywhere when form is closed.