Date is a reserved word. Should not use reserved words as names for anything. Unexpected things can happen. May or may not be cause here.
Otherwise, see nothing wrong. Post SQL statement. If you want to provide db for analysis, follow instructions at bottom of my post.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Thank you. I updated two fields that were named using "reserved words." Query still not working. Attached (as per your instructions) for analysis. The query in question is "MAXodo_FY19_Q3".
re-read question and deleted.
First & Last don't always produce what you expect. Max/Min are more reliable
In this case, you should use Where instead of Last for the Date field in the aggregate query.
Doing so gives the expected 9 records instead of the 7 you hadCode:SELECT Maintenance.License, Max(Maintenance.Odometer) AS MaxOfOdometer FROM Maintenance WHERE (((Maintenance.DateOfService) Between #1/1/2019# And #3/31/2019#)) GROUP BY Maintenance.License ORDER BY Maintenance.License;
Ooops, case of seeing what you expect - I did not notice the Last. I agree, Last and First are not reliable in most situations.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Wow. Yes, that works, thank you very much!
I suppose as a side note.... As a person just learning this stuff, how common is it that "X doesn't work like you expect"? I feel like that's going to be a real stumbling block in learning to use Access, if I can expect to keep running into things that logically seem as though they'd work a certain way, but turn out not to....
If I may, when you're presented with something that you've never reviewed for its usage, meaning, intent, etc. in Access, don't just use it - research it. While doing so with Last may not have revealed what's been told here, so many times people grab code, functions and methods and try to use them with no understanding of what those methods are supposed to do. However, even something as inconspicuous as Last ought to be researched. In other words, assume nothing and you will learn more and learn it faster than stumbling through an exercise.
Good advice, thank you.If I may, when you're presented with something that you've never reviewed for its usage, meaning, intent, etc. in Access, don't just use it - research it. While doing so with Last may not have revealed what's been told here, so many times people grab code, functions and methods and try to use them with no understanding of what those methods are supposed to do. However, even something as inconspicuous as Last ought to be researched. In other words, assume nothing and you will learn more and learn it faster than stumbling through an exercise.