Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Unanswered: all records between today and 90 days ago

    Someone please help !!!???!!!

    I am trying to query my departed employee table and show all records for the last 90 days.

    I tried this in the criteria, but it does not work:

    Between DateAdd("d",-90,Date()) And Date()

    Someone please help

    Thank You

    V/R
    D.Williams

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by williams9969
    I tried this in the criteria, but it does not work:

    Between DateAdd("d",-90,Date()) And Date()
    Hi
    What's actually happening ? Does your query run and return any records ? Does it just return an error message ?

    The obvious question is how is your date field set up ? It must be set up as a date type not a string.

    Chris

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    Try using Now() instead of Date().



  4. #4
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by williams9969
    Someone please help !!!???!!!

    I am trying to query my departed employee table and show all records for the last 90 days.
    Try this as your Where statement, either Date() or Now() should work fine.
    WHERE (((tblEmp.TermDate)>(Date()-90)));

    This is saying include all dates where the date is GREATER Than the current date - 90. Use your correct Table and Field name of course.
    ~

    Bill

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    cyberlinx, savbill, i think howey had the best answer

    let's wait to see if williams9969 comes back with some more info, such as that perhaps the field is not a date/time field after all, or exactly what "doesn't work" means

    Between DateAdd("d",-90,Date()) And Date() should work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2004
    Posts
    5

    date() and 90 days ago

    ok, i appreciate you guys help.

    i am a novice user so.....

    I have a table of departed employees. I need to see the records of employees that have departed in the last 90 days. The format for the date is dd-mmm-yy. I am thinking that may be the problem, but i don't know. I don't get an error, I just get a blank query. When I remove the criteria, i can see everyone.

    thank you

    d.williams

  7. #7
    Join Date
    Oct 2003
    Posts
    706
    That BETWEEN clause ... the original attempt ... should be the "right" answer. Check to see what values are actually being produced and do some queries of your own. It could be the data or data-type.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  8. #8
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by williams9969
    ok, i appreciate you guys help.

    The format for the date is dd-mmm-yy. I am thinking that may be the problem, but i don't know. I don't get an error, I just get a blank query. When I remove the criteria, i can see everyone.
    d.williams
    as howey said "The obvious question is how is your date field set up ? It must be set up as a date type not a string."

    The date format of 'dd-mmm-yy' is not a preset format under the Date type so that suggests the field is not set as a Date/Time format. If it is a Text type field you will not get the expected results using date criteria. You can check this by looking at the Table in the Design View. Under the Data Type you should have Date/Time selected? But don't change this if it is not Date/Time as you may loose some formats, so Test first. the best way to update the field may be to create a new field with the correct data type then run an update query to populate the dates from the current Date/Text field applying the correct formatting. Then change any forms/queries as needed to work with the proper date format. Once you have a Date Type field either the Between function or the Greator than function should give you the results your looking for.
    Last edited by savbill; 09-04-04 at 11:02.
    ~

    Bill

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let's say you have a table of character date strings like this:
    Code:
    table: chardates
    charfield
    03-apr-04
    04-mar-03
    09-sep-11
    09-sep-33
    31-feb-02
    if you just treat the character field as a date, access will attempt the conversion for you, and if the values allow, it will figure out which numeric portion is the day and which is the year, but where there is ambiguity, it assumes that the year comes first

    so, will access convert these strings to a date value correctly? let's see, by adding 0 days

    (adding 0 days will barf if the value being added to is not a valid date)

    now, obviously, that last one's an invalid date, unless 31 is the year and 02 is the day
    Code:
    select charfield
    , dateadd("d",0,charfield) as newdate0   
    from chardates
    
    charfield  newdate0
    03-apr-04  2003-04-04
    04-mar-03  2004-03-03
    09-sep-11  2009-09-11
    09-sep-33  1933-09-09
    31-feb-02  1931-02-02
    clearly, it guesses correctly -- not only for 31-feb-02, but also for 09-sep-11 versus 09-sep-33

    therefore, in order to fix the "ambiguous" values, you should explicitly insert the century:
    Code:
    select charfield
    , left(charfield,7)
      &iif(right(charfield,2)>'49','19','20')
      &right(charfield,2) as newchar1
    from chardates
    
    charfield  newchar1
    03-apr-04  03-apr-2004
    04-mar-03  04-mar-2003
    09-sep-11  09-sep-2011
    09-sep-33  09-sep-2033
    31-feb-02  31-feb-2002
    will these strings convert successfully?
    Code:
    select charfield
    , left(charfield,7)
      &iif(right(charfield,2)>'49','19','20')
      &right(charfield,2) as newchar1
    , dateadd("d",0,
        left(charfield,7)
        &iif(right(charfield,2)>'49','19','20')
        &right(charfield,2)
             ) as newdate1
    from chardates
    
    charfield  newchar1      newdate1
    03-apr-04  03-apr-2004  2004-04-03
    04-mar-03  04-mar-2003  2003-03-04
    09-sep-11  09-sep-2011  2011-09-09
    09-sep-33  09-sep-2033  2033-09-09
    31-feb-02  31-feb-2002  #Error
    as the kids say, "wa la"

    therefore, you need to add a new date/time column to the table, then update the entire table, and use the formula provided above for newdate1 to set the value of the new date/time column, after which you can remove the text column
    Last edited by r937; 09-04-04 at 11:54.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2003
    Location
    Lebanon
    Posts
    44

    Between Date() And Date()-90

    you need to create a query, and put in the criteria area Between Date() And Date()-90.
    Dani_30

Posting Permissions

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