Query problem using "last" (1 Viewer)

[arnelgp]

Why do you think that Access and Excel have different date references for day 0 of their internal calendar?

just tested it both, in Excel VBA and Access VBA:

format(cdate(0),"mm/dd/yyyy")

they both return:

12/30/1899

 

[The_Doc_Man]

Arnel, I first opened Excel and Access simultaneously. I opened the VBA window, opened the immediate window, and did a debug.print on 0. In Excel, I put 0 in slot A1, put formula =A1 in slot B1, and then formatted column A as date, column B as number.

Next, I asked Edge/Copilot about the date differences, which it acknowledged. Here is the first part of what it said.

Therefore, I have a reason to say what I said about dates. Yes, it might be hijacking the thread, but if there is erroneous information, I think it should be corrected before returning to the primary issue.

 

[arnelgp]

i don't know why are comparing excel against access.
just prove that Last("FieldName") is not returning the "Last saved New record".
you can also create a query from the table, sort it Asc or Desc (if you want).
then on vba->immediate window:

?DLast("FieldName", "YourQuery")

 

[sonic8]

you will notice, it is giving the same results over and over.

Run Compact&Repair and you might notice it no longer gives the same result.

 

[arnelgp]

Run Compact&Repair and you might notice it no longer gives the same result.

that is the catch, c&r, purge deleted records and reset the table, so Last() will not be reliable.
it also resets all queries execution plan, i think.
but i don't always use c&r, it might do harm than good, specially if power interruption occurs.

 

[DickyP]

Doing some archaeology on JET (in the Jet Database Engine programmer's Guide pub in 1997!) I found a reference to the Last (and First) aggregate functions, which explicitly says it (they) returns the last value of a set retrieved before formatting (eg, sorts) are applied.

 

[isladogs]

Being as this thread has already been severely hijacked, I'll compound it a bit further.

Regarding Excel zero dates, it is necessary to distinguish between calculations made in Excel worksheets and the VBE

As you can see above, in the Excel worksheet, day 1 is shown as 1900-01-01 and it has a nonsense date 0 of 1900-01-00.
Negative dates such as -1 aren't accepted
It also wrongly includes a leap date 1900-02-29 as day 60

Dates in the VBE start at 1899-12-30 for day 0 (same as in Access) and do not include the incorrect leap date.

The result is both calculations are identical from 1900-03-01 onwards in Excel
In Access, both the VBE & Access Expression service give consistent values for ALL dates. Access also allows for negative date values such as -1

CoPilot is therefore incorrect about discrepancies between date values in Access & Excel (from 1900-03-01 onwards). It is also incorrect in referring to VBA in the summary shown in post #42

 

[The_Doc_Man]

The diversion started, I guess, in response to a comment about uniformity of certain product behaviors and I got snarky because I have not found MSFT to be uniform in their treatment of certain products & situations. Sorry for the diversion, but I had a weak moment. The temptation was too great.

Getting back to the original topic - we have pretty much beat this horse to death. We can leave it by saying there are valid reasons to not use FIRST/LAST either as the DOMAIN aggregate version or the SQL aggregate version of those functions. They are not always predictable in what record they return, including whether the expressed or implied domain is a table, a query, or an ordered query.

The suggestion was made that FIRST and LAST should not have been retained as aggregate options. Their retention comes back to the idea that sometimes, MSFT doesn't change things even if they appear to be somehow "broken." MSFT will talk about "backwards compatibility" though that argument - particularly in regards to Win10/Win11 transitions - seems awfully hollow. The "time" diversion was just an example of that hollow approach.