find last recorded date in a table (1 Viewer)

puthenveetil · Sep 20, 2005

Hi all,

I have a problem finding the last date entered in a table..The purpose is to find the record which entered last in a table. Is there any way to find the last record with date ? I am attaching a sample db with this. Please look that if my question is not clear here..

Thanks in advance

thanks

Brianwarnock · Sep 20, 2005

Do you mean the latest date in the table ie max date or the date of the last record entered ie Last date ?

I would use a query and base the form on that selecting the date field with appropriate aggregate function. Oh and I wouldn't use date as afield name it is a functionname and will cause you problems in the long term.

Brian

puthenveetil · Sep 20, 2005

Hi Brian,

I would like to find the last updated date in a field.

thanks

Brianwarnock · Sep 20, 2005

Then you use Last. Your query is simple

SELECT Last(Table1.date) AS LastOfdate
FROM Table1;

then base your form on this query, this will give you the last date entered into the table.

Brian

Rich · Sep 20, 2005

Last might not give you the correct record, you should use Max on the date field

Brianwarnock · Sep 20, 2005

He wants the last updated record not the last date.

Brian

Pat Hartman · Sep 21, 2005

Last() returns the last record in the recordset. This has nothing to do with the last updated record, the last added record, or the record with the max date. Queries return unordered sets unless you specifically order them by something unique. That is the ONLY to predict with absolute certainty which record will be First and which will be Last.

If you want to find the last record added to a table, you need to use an autonumber and find the Max(yourautonumber) value - NOT the last unless you order the recordset by autonumber and that is a silly waste of resources when Max() on the primary key will only need to read an index, not sort an entire table.

If you want to find the record with the most recent date, you need to use Max(SomeDate). Be aware that unless there is a unique index on SomeDate, this query could return more than one row.

If you want to find the last updated record, that's a little tricky. You could add a LastUpdateDate field and populate it with Now() in the BeforeUpdate event of the Form. You would then use Max(LastUpdateDate).

So - there you have it. In none of these cases is it appropriate to use Last().

puthenveetil · Sep 21, 2005

Thanks Pat, I will give a try ......

Thanks

puthenveetil · Sep 21, 2005

Pat,

That is what I wanted....thanks

Thanks

Brianwarnock · Sep 26, 2005

Just got back to the forum and feel that I should apologise for the stupidity of my last response, I'll blame a senior moment

I never use Last but am puzzled by Pat's answer and hope that she can find time in her busy life to clarify for me.

Quote from Pat

Last() returns the last record in the recordset. This has nothing to do with ............, the last added record

,

What do you mean by the last record, I was not aware that the tables had the concept of the last physical record?

Quote from ACCESS help

You use the First or Last functions to return the first or last record in the group you are performing calculations on. These functions return the first or last record as it was entered in chronological order. Sorting the records has no effect on these functions.

This implies, atleast to me and testing suggests that it is correct, that I could use Last to find the last record added in any group of records, which I feel is the same as using Max on Autonum. I'm not suggesting this is the way to go , one should design a more positive approach but occasionally one inherits poor design.

Brian

Pat Hartman · Sep 27, 2005

I would be interested in seeing the context of the help you quoted. I'm guessing that it actually comes from an Excel help entry.

Many people are fooled into thinking that queries, when unsorted, return records in primary key sequence. This happens for smallish tables and even larger ones immediately after a compact. The compact in addition to all the other stuff it does, rewrites all tables into physical sequence by primary key. This is similar in effect to a clustered index that the "larger" RDBMS' support. The database engine doesn’t go out of its way to sort a recordset, it simply returns records in the order in which it finds them which may or may not be the order you expect.

Access is probably the only true relational database that offers the First() and Last() and DFirst() and DLast() functions. They really don't have any meaning in a relational database.

Remarks (from DFirst() and DLast() entries)
Note If you want to return the first or last record in a set of records (a domain), you should create a query sorted as either ascending or descending and set the TopValues property to 1. For more information, see the TopValues property topic. From Visual Basic, you can also create an ADO Recordset object and use the MoveFirst or MoveLast method to return the first or last record in a set of records.

Remarks ( from First() and Last() entries)
The First and Last MoveFirst and MoveLast methods of a DAO Recordset object. They simply return the value of a specified field in the first or last record, respectively, of the result set returned by a query. Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary.

Brianwarnock · Sep 27, 2005

Thank you for replying Pat.
My quote was from Access help Answer wizard "aggregate functions" then select "about calculations in a query" then you can read abot First and Last.

However having read your response I think I will continue not to use these functions opting to get what I want by design.

Brian

Pat Hartman · Sep 28, 2005

I'm still puzzled. I tried using the answer wizard which I normally turn off and found this entry:

First, Last Functions
Return a field value from the first or last record in the result set returned by a query.

Syntax
First(expr)

Last(expr)

The expr placeholder represents a string expression identifying the field that contains the data you want to use or an expression that performs a calculation using the data in that field. Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate functions).

Remarks
The First and LastMoveFirst and MoveLast methods of a DAO Recordset object. They simply return the value of a specified field in the first or last record, respectively, of the result set returned by a query. Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary.

See Also
Calculating Fields in SQL Functions SQL aggregate functions
ORDER BY clause

Which says basically the same thing as the other two entries.

I am using A2003 and keep it updated.

I have lodged many complaints with microsoft regarding Access help. The problem is with the search engine unfortunately which is not under the control of the Access team. All of Office shares the search engine.

Brianwarnock · Sep 28, 2005

I'm guessing that you're in VBA , I'm not.
Does that make sense?
just tried it for myself as one should

, but could only find Dfirst Dlast.

Brian
A2002 SP3

Pat Hartman · Sep 28, 2005

No, I did the search from the database window side. The first search I tried from the VBA window because that's where I expected to get function information (silly me). I only found the domain functions. I closed help and went back to the db window and found the First/Last entries.

I'm sure they made some help changes between 2002 and 2003. It appears that they actually corrected something.

find last recorded date in a table (1 Viewer)

puthenveetil

Registered User.

Attachments

Brianwarnock

Retired

puthenveetil

Registered User.

Brianwarnock

Retired

Rich

Guest

Brianwarnock

Retired

Pat Hartman

Super Moderator

puthenveetil

Registered User.

puthenveetil

Registered User.

Brianwarnock

Retired

Pat Hartman

Super Moderator

Brianwarnock

Retired

Pat Hartman

Super Moderator

Brianwarnock

Retired

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread