I have a query that has a date value and a text comment associated with the date (notes entered on a specific date). I need to set a criteria which gives me the note entered. Using “Last” in the total field doesn’t work. Is there some way I can set the criteria to give me the greatest value? If I put that criteria in the date field, I should be able to see only the most recent note.
If I understand your question correctly, you need a correlated subquery to do this. Either more than one query, joined together, which is the typical way to do this; or, you can put a SQL query in the criteria row. Something like this:
=(SELECT Max([CommentDate]) From tblComments)
I ended up with more than one query and used Max instead of Last. I’ve got it working properly now (it seems. I’m sure one of the users I built this for will come back and want something changed).