Find specific record in subreport

[hpHHI]

Hello,



Perhaps I'm attacking this problem the wrong way, but here's what I'm looking at:

Our Database tracks different measurement data for students. I've got a report that I want to use to track students progress. Each type of measurement is listed under a different table, which I used subforms for in the entry form (with each record of the main form pertaining to one student) and am now using subreports for in the output report.

The problem I'm having is I want to find the data from the earliest measurements to use in the subreport footer. Each measurement has a date attached to it. I can grab the minimum of the date field, but I can't figure out how to get the related measurement to appear in a separate text box. I've tried using DLookup to get to the original record in the table, but either I can't get the syntax right, or it won't work the way I want it to.

Please assist, or let me know if I've completely got the wrong idea.

Thanks.

[June7]

Maybe like:

SELECT * FROM tablename WHERE [datefield]=DMin("[datefield]","tablename","StudentID=" & [StudentID);