Display last entry in in field footer.

[Bongard]

Hi, I have a report that is on loan status. In the detail I have the
fields Loan#, Date, Amount, LoanName, City, State, Analyst, and Notes.
I have set up grouping by loan number and I will only display the group
footer in the report with all the same fields. I have a sum of [Amount]
to get the current balance and I want to display the last note that was
put on the account with text in it. For example, sometimes transactions
are input and no note is added.
(I have sorted first by loan number, then by note date descending)

Right now if a note was not entered on last transaction

Detail(Not displayed in actual report)
Notes
9/20/06 (Blank)
9/19/06 (Blank)
9/17/06 "Note entered on a transaction"
9/16/06 (Blank)
Loan Number footer (What shows in report)
9/20/06 (Blank)

I want it to show
9/20/06 "Note entered on Transaction" (or the last note on the
account)


Does anyone have any suggestions on how I would go about doing this?


Thanks much!

 

[Jeff L]

To get the last note entered on a loan, try putting this in a field on
your report.
=DLast("Notes", "YourTableName", "[Loan#] = " & [Loan#] & " And Notes
is not null")

Hope that helps!

 

[Marshall Barton]

Hi, I have a report that is on loan status. In the detail I have the
fields Loan#, Date, Amount, LoanName, City, State, Analyst, and Notes.
I have set up grouping by loan number and I will only display the group
footer in the report with all the same fields. I have a sum of [Amount]
to get the current balance and I want to display the last note that was
put on the account with text in it. For example, sometimes transactions
are input and no note is added.
(I have sorted first by loan number, then by note date descending)

Right now if a note was not entered on last transaction

Detail(Not displayed in actual report)
Notes
9/20/06 (Blank)
9/19/06 (Blank)
9/17/06 "Note entered on a transaction"
9/16/06 (Blank)
Loan Number footer (What shows in report)
9/20/06 (Blank)

I want it to show
9/20/06 "Note entered on Transaction" (or the last note on the
account)

Make the group footer text box for the note unbound. Then
add a line of code to the detail section's Format event:

If Not IsNull(Me.Notes) Then Me.txtFtrNotes = Me.Notes