DLookup based on Average, no result (1 Viewer)

I have created a report that has averages for some groupings. I then use DLookup to retrive marks assaigned to those specific averages from another table. The correct result only shows for the last record on the report. It is as if the result from =Avg() is calculated after the field with the DLookup function executes. If I use a field withouth a calculation to test my DLookup function everything works out fine.
How can I force one calculation to be completed before another or how to I get Access to execute the DLookup function after a second or even put that code behind a button.
I want to use the average in the report to look up the primary key in another table and then return what is in the field corresponding to that ID into a text field.
Help please

I've read this several time but am struggling to understand how the different parts link together. Th fact that there have ben almost 20 views with no other replies may indicate I'm not alone.
Please try explaining again and add screenshots or attach your db. Zip first as you have less than ten posts.

OK I will try to explain better.
The first attachment shows the report and how the fields I want to show to display my DLookup result are empty and the second attachment shows my DLookup. It is as if the Average function on which the DLookup relys on doesn't get calculated until after the DLookup. Do I have to create a temporary table or can I use an event to force the report to populate the fields. Still, help please!:banghead:

Yes I can see why it wouldn't work reliably.
You're trying to look up values using criteria that aren't stored data.
Suggest you use an aggregate query to calculate the averages.
Then base your DLookup on that query rather than just the table.
That way you know the averages are calculated first

Hi Isladogs
Thank you very much that has worked

Excellent. Good luck with the rest of your project.