Show Most Recent Information for each Problem Item for One Company

[Aubrey]

My database has one table, tblPlanData, with Fields: CustID, ProbItemNo,
CallDate, and CallNote (Memo). I would like to run a Report for One CustID
that shows only the most recent CallNote for each ProbItemNo.

 

[BruceM]

Base the report on a TOP 1 query. To do this, in query design view sort the
records by the date/time field. Click View > Properties (or click Alt +
Enter). Set the Top Values to 1. You could do this in SQL view by adding
"TOP 1" (without the quotes) after SELECT.

 

[Duane Hookom]

Group your report by ProbItemNo with a group header. Sort next by CallDate
Descending. Add a text box to your report's detail section:
Name: txtCount
Control Source: =1
Running Sum: Over Group
Visible: No
Add code to the On Format event of the detail section:
Cancel = Me.txtCount>1

 

[Duane Hookom]

I think a non-code method would be to place all of your fields into the
ProbItemNo group header. This would assume the report is sorted by CustID,
ProbItemNo, and CallDate (Desc). Then hide the detail section.

 

[Aubrey]

Elegant! Clear, and easy to maintain. Report already had OnOpen and OnClose
Events, so adding OnFormat was a snap. Thanks!

 

[Robbie Doo]

This code works great, thank you. However, when there's no data it gives me
an Run-Time Error Code "2427". Is there a way to fix this?

 

[Duane Hookom]

I am surprised that a report with no data would run the On Format event code
of the detail section. Try change the code to:
If Me.HasData Then
Cancel = Me.txtCount>1
End If

 

[Robbie Doo]

Thank you Duane, I got that from your earlier posts and did the trick. Works
like a charm.

 

[Robbie Doo]

Is it possible to use this for information from Sub-reports to main?

 

[Duane Hookom]

It might be best to start a new thread with complete information about your
situation.