Query problem using "last" (3 Viewers)

[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.