Query problem using "last" (2 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.

 

[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

Wikipedia is therefore incorrect about discrepancies between date values in Access & Excel (from 1900-03-01 onwards)