Problem with retrieving last record

[Guest]

I have a table that lists a number of transactions for recording the rental of inventory items
Basically, at times the users would like to know the last time a piece of inventory item was rented, which would then also retirieve the time and date that the item is supposed to be returned.
I have the following fields in the table. TransactionID, ItemName, RenterName, StartRentalDate,EndRentalDate and TransactionType (i.e. Rental VS Return)
Let's say for example, someone comes to me and wants to rent a TV. I need to be able to QUERY the transaction table and find the last time that particular item was rented, when it was rented and when that item will be returned. The reason for this is that I would like to pull this information from within a form (basing a FORM on a QUERY)
But I am not sure how to pull the Last record for the item from the table. I tried the LAST option, but it wouldn't work. Basically, I will need to know the ItemName from the user (Combo Box selection), then allow me to find the last time that Item was rented and when it will be returned

THANKS,

 

[Allen Browne]

Try Max instead of Last.

Last is nearly useless. It is very inefficient, and effectively means, "Load
all the records, and give me the value from the last one". If you have not
specified a sort order, that's utterly useless, and if you did specify a
sort order it would have been *much* better to sort descending and grab the
first one.

DMax() will give you the most recent date it was rented. You could put a
text box on your form to display the last time the item was rented. Its
ControlSource would be:
=DMax([StartRentalDate], "MyTable", "ItemName = """ & [SomeTextBoxNameHere]
& """")

If you want to get the value of a different field, there is an extended
DLookup() in this link:
http://allenbrowne.com/ser-42.html
For example to get the TransactionID of the most recent record for the item:
=ELookup([TransactionID], "MyTable", "ItemName = """ & [SomeTextBoxNameHere]
& """", "StartRentalDate DESC")

If you are doing this for every record in a query, the functions are slow,
and there are better alternatives in this link:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table that lists a number of transactions for recording the rental of inventory items.
Basically, at times the users would like to know the last time a piece of

inventory item was rented, which would then also retirieve the time and date
that the item is supposed to be returned.

I have the following fields in the table. TransactionID, ItemName,

RenterName, StartRentalDate,EndRentalDate and TransactionType (i.e. Rental
VS Return).

Let's say for example, someone comes to me and wants to rent a TV. I need

to be able to QUERY the transaction table and find the last time that
particular item was rented, when it was rented and when that item will be
returned. The reason for this is that I would like to pull this information
from within a form (basing a FORM on a QUERY).

But I am not sure how to pull the Last record for the item from the table.

I tried the LAST option, but it wouldn't work. Basically, I will need to
know the ItemName from the user (Combo Box selection), then allow me to find
the last time that Item was rented and when it will be returned.