Most Current Date Query

[DCXtreme]

Hi there, I am trying to put a query on my form which selects the most recent date of a members attendance from an attendance table and displays it in a text box, the code I am using is:

Private Sub Text41_BeforeUpdate(Cancel As Integer)
SELECT MAX(Attendance.AttendDate) as "LastAttended"
FROM Attendance
WHERE Attendance.membershipNo = Forms!Customer.sbfAttendance.Form.membershipNo
End Sub

Customer is my main form and Attendance is my subform. I think the mistake is that I dont have a field called last attended (you are not supposed to store data like this are you) and I need it to display in my text box (text41) on the main form

Thanks in advance,

Jon

 

[Bat17]

You should be able to do this using Dmax as the recordsource for the control
=DMax ("[AttendDate]", "[Attendance]", "[membershipNo] =" & Forms!Customer.sbfAttendance.Form.membershipNo
)

HTH

Peter

 

[DCXtreme]

Perfect, Thanks!
Is there anyway I could encorporate that code into my query? Or do I have to try the SQL code above

 

[Bat17]

you can use Dmax() in a query but sql is generaly quicker.

Peter