Get the Last Date from a table

[member]

Hi,

I want to know how i would get the last date from a table.

I've tried 'max', but that just sorts the order of the dates.

What i need is for only the last date to be selected.

e.g.
24/10/09
27/10/09 - this should be selected
14/05/09

I hope you get what i mean. I'm not too good with Access, i'm learning, so please excuse if i ask stupid questions with obvious answers.

Thanks.

 

[Steve R.]

You need to post your code to see what may be wrong with it. Actually, as I am writing this, it occurs to me that you may be using the wrong "MAX" function, you need to use "DMAX".

Furthermore, dates can be an issue since they sometimes need to be bracketed with the "#" sign. Did you look-up in Access help on how to use "dates" and domain functions such as "DMAX"

 

[member]

You need to post your code to see what may be wrong with it. Actually, as I am writing this, it occurs to me that you may be using the wrong "MAX" function, you need to use "DMAX".

Furthermore, dates can be an issue since they sometimes need to be bracketed with the "#" sign. Did you look-up in Access help on how to use "dates" and domain functions such as "DMAX"

I can't find 'DMAX' in the list, and when i do type it in, it says that it isn't in the list so i can't select it. I have the 'Totals' Button activated btw.

PLease correct me if i am taking the wrong approach.

Thanks Steve ( or anyone else)

 

[wazz]

- you can leave the dropdown on 'groupby'.
- sort the list descending (most recent first).
- go to the 'totals' button. right beside it (on my menu) is a little drop down list, 'top values' (hover to see the tooltip). type '1' in there. that will get you the top '1' on the list.

 

[raskew]

Hi -

Try the Top predicate, e.g.

SELECT TOP 1 YourTable.*
FROM YourTable
ORDER BY YourTable.YourDate DESC;

...would return the record(s) with the latest date.

HTH - Bob

 

[member]

- you can leave the dropdown on 'groupby'.
- sort the list descending (most recent first).
- go to the 'totals' button. right beside it (on my menu) is a little drop down list, 'top values' (hover to see the tooltip). type '1' in there. that will get you the top '1' on the list.

Thanks wazz ur a star, much appreciated. and also thank you raskew, i didn't use yours cos it seems more complicated and would prefer the one i used. Thanks anyways for ur time.