hi there
Hope someone can help me out here.
I have a Access 2003 database which I access through ASP website.
I want to count the numbers of records I had one year earlier then the present date. My code looks like this

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorType = adOpenStatic
objRS.ActiveConnection = Con

objRS.Open="Select * FROM NEWSTUDENTS_2011 WHERE
STUDENT_status < '2' and entry_date < Date()-365"

number_student_2011 = objRS.RecordCount

I guess my use of date format is terrible wrong - but hours of searching and trying different alternatives - haven't worked so far. :-(

The recordcount I recieve is too big ....
whats wrong ..?
thanks for any response

Otto Leholt

Change:
to:
Note: If STUDENT_status is defined as a numeric data type, also change:
to:
Note2: You'll possibly need to use:
before retrieving a significant value from the RecordCount property of the RecordSet.

Hi - thanks for your quick response..

I used your suggestion - in this way :

objRS.Open="Select * FROM newstudent_2011 WHERE entry_date < "&DateAdd("yyyy", -1, Date)&" AND student_status < '2' "
objRS.Movelast

antal_elever_2011 = objRS.RecordCount

BUT I still get too many recordcounts ... so still something here isn't working right :-(

ohh - I just realized that my datafield 'entry_date ' is a TEXT field and not the date type - I guess that may be the problem or what ...?

hi again -
now I changed my 'entry_date' field from TEXT to Dateformat type
and so my original code :

entry_date < Date()-365

actually seems to work .. so far only tested on localhost , but anyway ..:-)
But I do admit that this may not be the right way to work around with date ..

thanks for your response so far :-)