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

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


6 Years
Discussion Span
Last Post by izyrider

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

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).


Edited by izyrider: typo



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()


...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

Edited by izyrider: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.