trouble with "LastOf" in a query

[kcrad]

I am running a query that returns the last entry in a table. I am using the
"Last" function in the Totals row. The query result is used in a form
control to set the last value entered as the default for the field. The form
is used by multiple users on several different machines.

My problem is this: Once in a while the LastOf query gets stuck on one
record and ignores anything else that is entered in the table. If I delete
that record, the query returns the value in the field previous to the deleted
one. It's like the query doesn't see anything in the table after the value
that it gets stuck on. I have fixed the problem once by deleting everything
in the table and then copying it back in(essentially I just gave each record
a new autonumber).

I'm sure that something is triggering this event, but I don't know what it
could be. Has anyone seen this before? Is there a better way that I should
set up a default value formy control?

Thanks

kcrad

 

[Ken Snell \(MVP\)]

The Last aggregate function in a Totals query is not giving you the "last
record" in the table -- the query will select the last record accessed,
which may or may not be the "last" record.

If you want the last record, you'll need to identify what "last" means --
for example, the most recent date/time, the highest value in a sequentially
numbered field, etc. -- and then specifically tell the query to get the data
from that record. How the query would be structured to do this will depend
upon the rest of the query's construction.

Post more details about what you're wanting to do, including table
information, etc., and we can assist you in setting up a desirable query.

 

[Jerry Whittle]

A couple of thoughts.

1. Last has no meaning unless you have the records sorted. Does the query
have a Sort or Order By clause? If not, that's the problem. If it does,
possibly the last record in the sort order isn't changing. For example if you
are sorting by Date and there are multiple records for that Date, then any of
them just might be considered last.

If sorting on an autonumber, it's possible that the autonumber did not
increment to a larger number as you expected. Is the autonumber field the
primary key or unique index in the table? If not it's possible that there are
duplicates especially if you are appending records to the table.

I find that sorting descending on a Date/Time field that includes the time
with a default value of Now(), will give me the last record entered into a
table. It's unlikely that two records will be added in the same second unless
doing append queries. Actually I use the Max function with a Date/Time field
as it doesn't need to be sorted to bring up the "last" record. Either way
make sure that the Date/Time field is indexed for performance reasons.

2. If in a multi-user environment, someone might be adding or changing
records as you are using the query. Usually re-running the query fixes it.

 

[kcrad]

Thanks guys - I got it. I misunderstood the Last function. I fixed it by
using Max on my autonumber and then taking information from that row.

You've been a great help!

Kcrad