Get latest record

[Always_Learning]

Hi There,

In my Access app I need to get the latest record added of an item from a MS Sql table and check the period between now and the date saved in the record.

So my guess is:
Select TOP 1 * from tbl_StockItems
Where StockId = Loc_StockId

I then need to check the days between tbl_StockItems.LastStockDate and Today.

How would I write an Access VBA query to give me the amount of days and put that number into a variable.

Thanks for your help.

Best Regards,

 

[DavidAtWork]

Is StockId the PK for tbl_StockItems, if so it won't be necessary to use Select TOP... because there should only be one record with that StockID anyway.
Also is the table tbl_StockItems linked in the database i.e. you have direct access to the table, then you could use
myVariable = Date - DLookup("[LastStockDate]","[tbl_StockItems]","[StockId] = " & Me.Loc_StockId)

assuming Me.Loc_StockId is a field on a form.

David

 

[namliam]

How about "proper" SQL like:

Select sysdate - max( tbl_StockItems.LastStockDate )
from tbl_StockItems

 

[Always_Learning]

Hi David,

Thanks for your reply and help.

No, There is not a primary key.
Yes the table is linked in the database.

I like your suggestion but there are many items in the table with the same stockId.

Thanks again for your help.

Best Regards,

 

[Always_Learning]

Hi Namliam,

Thanks for your reply.
The proper sql is good but how do I get the result into a variable in Access so I can check if the value is above a criteria I have set.

Also, there are many items in the table with the same stockId.

Thanks for your help.

Best Regards,