TextBox to display last record on subform 1

[CountyArchitect]

Greeting:

I have a main form of members with a filtered subform with individual payments made by the member (filtered) from the main form.

There is a textbox that preforms a DSum of all payments made by that particular member on the main form. What I would also like to see is the last payment amount made by this particular member on the main form.

I tried the following--but it returns a random record from the filtered set...not the last record.

=IIf(IsNull([MemberID]),0,DLast("[PaymentAmount]","Payments","[MemberID]=" & [MemberID]))

What am I missing?

Thanks much for the help.

 

[dhookom]

DLast() and Last are basically random. There is no order to records. They are like marbles in a bag with no first or last. You would need to change the where condition to include the maximum date and use DLookup(). I would probably create a query qgrpLastPayments that returns the most recent payment information for each MemberID

SELECT *
FROM Payments
WHERE PaymentDate = (SELECT Max(PaymentDate) FROM Payments P2 WHERE P2.MemberID = Payments.MemberID);

Then your DLookup would be

DLookup("PaymentAmount","qgrpLastPayments","[MemberID]=" & [MemberID])

Duane
Hook'D on Access
MS Access MVP

 

[CountyArchitect]

dhookom:

That worked great! Thanks.