Retrieve Last query not working as intended

[marabak]

Hey guys. I wrote a massive email with lots of details etc. about my issue just, but I was logged out before it posted and I have lost it all. I'll try to be more concise this time.

I've been left with a database someone else built over two years ago and an issue has recently cropped up. Part of the database involves retrieving the last date in a table and calculating the time from Now() back to that date. This isn't currently working and appears to be stuck at a particular date.

Doing some probing, the database uses a query (called "LP CT Weekly") which searches a table (called "CT Weekly QA") for the last entry in the "Date Performed" column, after sorting by ascending. The SQL code is

SELECT Last([CT Weekly QA].[Date Performed]) AS [CT Weekly]
FROM [CT Weekly QA]
ORDER BY Last([CT Weekly QA].[Date Performed]);

This is similar syntax to other queries which do the same job on different tables. Currently the returned value is 16/02/17 despite three other entries appearing more recently. This issue has been ongoing for three weeks now, with the 16/02/17 being returned every time, so it isn't anything to do with returning one value above the last etc. The query just appears to be ignoring anything more recent than the 16/02/17. This has a unique ID of 162.

The table being called has the "Date Performed" entry as type Date/Time and format Short Date.

I'm at a loss as to why the query has appeared to have maxed out at this date and is ignoring more recent dates. Manually sorting the table by Date Performed: Oldest to Newest has no effect.

Anyone got any clues or directions I should look in? Thank you very much.

 

[pbaldy]

Last() is unreliable, try Max().

 

[plog]

"Last" and "First" should be bludgeoned to death with a crow bar and set on fire in an alley. Do not use them, they don't do what any rational person expects them to do.

To get the latest value of your field, you would use the MAX function instead. No need for an ORDER BY clause because its only returning one record. If that doesn't work, make sure your "date" field really is a Date/Time field and not text.

 

[CJ_London]

LAST has no context without order - it is not necessarily the last entry made. And ordering by LAST will be ineffective.

Clarify what you mean by LAST - if last entry added, you need to have a timestamp field to order on. If you mean latest date, suggest use Max

 

[marabak]

Cheers for the replies guys. As I said previously, this isn't my database and there are quite a few things I would do differently. Using Max has definitely solved the issue now though.

Thank you

 

[pbaldy]

Happy to help!