Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    4

    Unanswered: count numbers of records untill a specific date

    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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Change:
    Code:
    entry_date < Date()-365"
    to:
    Code:
    entry_date < DateAdd("yyyy", -1, Date)
    Note: If STUDENT_status is defined as a numeric data type, also change:
    Code:
    STUDENT_status < '2'
    to:
    Code:
    STUDENT_status < 2
    Note2: You'll possibly need to use:
    Code:
    objRS.MoveLast
    before retrieving a significant value from the RecordCount property of the RecordSet.
    Last edited by Sinndho; 01-21-12 at 04:07.
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    4

    still I get too many numbers

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

  4. #4
    Join Date
    Jan 2012
    Posts
    4

    my entry_date field is a text field !

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

  5. #5
    Join Date
    Jan 2012
    Posts
    4

    Talking Now I got it right..

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •