yes dlookup what you are looking for. with dlookup you can find a value in a table based on a criteria.
Usage: Dlookup("LookupField", "YourTable", "Criteria")
The Function below finds age from YourTable with AValue given to function.
Public Function FindAge(AValue as Double)
FindAge = DLookup("age", "YourTable", CStr(Avalue) & " Between [From] AND [To]"
It looks like Dlookup will do what I need to to do, I've tried it in a form and it gives the values that I need correctly. So now its how I implement it.
The full syntax in my example is
=DLookUp("[Ageing]![Details]","ageing"," [Collection_notes2]![Days age] between [Ageing]![Age_lower_level] and [Ageing]![Age_upper_level] ")
Ideally (although against database design advice) I need this value in a field as it is used so much elsewhere in the database. The best way for me was if I could have the flexibility of Dlookup but implement it in a query that originally imports the data, How can I achieve this?