Value of field in record with most recent date (1 Viewer)

[m001]

I'll try to explain what I'd like to see on my form

A calculated field which contains the value of the field (fieldA) that belongs to the record with the most recent date.

Finding the most recent date isn't hard, I made a calculationfield called (MaxDatefield) with the calculation =Max([Datefield])
But I need to know what the value is of another field in that same record and I can't seem to figure out the calculation.

I've tried:

=(SELECT [fieldA] FROM [form] WHERE [Datefield]=[MaxDatefield])

But it just doesn't work

As the whole is a bit more complicated than this, I really need to be able to have the result through a calculation field (and not a query).

Please help!

 

[glynch]

Create one query to retrieve the maximum date value from the table which feeds the form. Use that query as a nested query within another query using the same table joined to the first query on table.DateField = query.Max(DateField), inlude fieldA in the second query. use that query to populate the textbox for fieldA on your form.

HTH