MAX YearMonth Value using SQL query in MS Access

[ria.arora]

Hi

How to get MAX YearMonth Value using SQL query in MS Access 2007. Pls note YearMonth is stored as Text

I'm executing below SQL and it's returning all the records from the table

Select MAX([YearMonth]) from tbl_Revenue_Assets_Split GROUP BY [YearMonth]

Output is coming:
YearMonth
201201
201202
201203
201204
201205

I want only 201205 (YYYYMM).

Any idea how to achieve this?

 

[ria.arora]

Please note I have tried CInt, CSng and CDbl e.g.
Select MAX(CINT([YearMonth])) from tbl_Revenue_Assets_Split GROUP BY CINT([YearMonth])
Error: Overflow

 

[plog]

When you use a GROUP BY clause you will get all distinct values of the field you GROUP BY. You will want this SQL:

Select MAX([YearMonth]) from tbl_Revenue_Assets_Split;

I'd also reconfigure tbl_Revenue_Assets_Split to not only store the Year and Month as numeric data, but in seperate fields as well. Additionally, do not name them just 'Year' and 'Month', add something more meaningful--possibly a prefix: i.e. AssetYear, AssetMonth.

 

[G37Sam]

YourAnswer = DLookup("max(yearmonth)", "tbl_Revenue_Assets_Split")

 

[ria.arora]

Thanks... Did silly mistake