get records with recent dates

[vn412]

Hi,

I m using vb.net with access 2007. I have field with datatype "datetime" and values in format dd/mm/yyyy e.g 8/2/2011. Now I have problem when i fire a query

SELECT *
FROM tbl_name 
WHERE tbl_name.datefield= (Select Max(tbl_name.datefield) from tbl_name));

I have some records with dates 11/1/2011 and some records with dates 8/2/2011 but I get wrong result records with date 11/1/2001 instead I want records with date 8/2/2011

Please help

Thanks

[izyrider]

your values are NOT in format dd/mm/yyyy!
**NOT**

Access datetimes are actually numbers (years dot fractional years since Access-defined Beginning Of Time (sorry if i forgot what Access-BOT is)).

you cannot reliably talk SQL to the Access database engine (JET or whatever) using dd mm yyyy. it will work fine for 1st January, confuse you on 11th January, and confuse you even more by "working" for 13th January.

either you adopt an american accent: mm dd yyyy
or you talk mock-ISO (that Access also understands): yyyy-mm-dd

your MAX(datetime) is probably working correctly :)

whatever you used to INSERT the datetime is probably broken (attempt to insert date-as-string using dd mm yyyy which Access will automatically interpret as mm dd yyyy whenever possible. Access will silently switch to dd mm yyyy when you get to the impossible 13th...31st month).

izy

[lolafuertes]

Can you be so kind to post the properties of the datefield in your database?

TIA

[izyrider]

:(

Access datetimes are days.fractionaldays (not years.fractionalyears as i said earlier) since BeginningOfAccessTime.

doesn't change my view that the problem is at your INSERT rather than your SELECT MAX()

izy

afterthought
...possibly the query optimiser will fix it for you anyway, but why not:

SELECT MAX(x.yourdatefield)
FROM yourtable x

instead of the subquery SELECT in your original