Problem with forming a query in Access

SELECT Date
FROM tablename
where Date = (Select MAX(fieldname)
FROM tablename);

type this in an sql query & run.

Even though Sub querys, as in the first answer, are sonetimes unavoidable to use, where possible, do not use them. They are much much slower, and cause a lot of network traffic as well.

In your case I would use Max:

In the query designer goto SQL view and copy this into the window, (or use it in your coded SELECT statement), replacing the myTable1,MaxField1 and myDateField names with the ones that you want.

SELECT Max([myTable1].[myField1]) AS [MaxField1], Max([myTable1].[myDateField]) AS [MaxDate]
FROM [myTable1];

You can do this in the Normal Design View window:
Select the table, Select the two fields, go to the menu point VIEW and select FUNCTION. For each field in the grid, select on the line "Function" the "Max" tem.You can add more fields but each have to have the "Max" function selected.

If you want to return the max items in a group then, in this case, you would use the Max function only on the one field that is to be maxed, and the GroupBy function on the other(s).

Sorry,
I mis-interpeted the question. You would only need to use the Group By statement when returning the Max for groups of records.

Yes, you need to do a Sub-query for this, as Ghost has suggested.
He gave the wrong answer probably becausehe typed it out of his head or too fast.
In the criteria, the fields need to be the same:

1. In the SQL window:

SELECT [Table1].[DateField]
FROM [Table1]
WHERE [Table1].AmountField)=(SELECT MIN([Table1].[AmountField]) FROM [Table1]);

Replace Table1 with your table name and AmountField with the field to find the Max on and DateField with the name of the date field to be returned.

OR

2. In the degign window
Select the two fields but remove the check in the Show check box to not show the AmountField in the results.

Then, in the criteria line under the AmountField - important that it is in this field - type in:

(SELECT MIN([Table1].[AmountField]) FROM [Table1])