Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Posts
    5

    Unanswered: query for date field less then 30 days back

    I am trying to query all those records that have a RecDate greater then 30 days back. The query

    WHERE ((Format([tblStorePrice.RecDate],"m/d/yyyy")) >= (Format((Date()-30),"m/d/yyyy")))

    returns no records. Interestingly though when I change the sign to be <= it returns records. Below is what it returns.

    1st column
    Format((Date()-30),"m/d/yyyy"))

    2nd Column
    ((Format([tblStorePrice.RecDate],"m/d/yyyy"))
    1st 2nd
    9/14/2002 10/11/2002
    9/14/2002 10/14/2002
    9/14/2002 10/9/2002
    9/14/2002 10/9/2002
    9/14/2002 10/8/2002
    9/14/2002 10/8/2002
    9/14/2002 10/8/2002
    9/14/2002 10/10/2002
    9/14/2002 10/8/2002
    9/14/2002 10/9/2002

    Please explain the correct way to compare to today's date.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    The format function is causing the problem - just use the date field and the date column. Are you looking for all records that are at least 30 days old (and older) ?
    Last edited by rnealejr; 10-14-02 at 19:11.

  3. #3
    Join Date
    Oct 2002
    Posts
    5

    Re: query for date field less then 30 days back

    I think you can check difference between current date and recdate should be <= 30 if you want records upto 30 days back.

  4. #4
    Join Date
    Oct 2002
    Posts
    5
    Originally posted by rnealejr
    The format function is causing the problem - just use the date field and the date column. Are you looking for all records that are at least 30 days old (and older) ?
    I had the recdate field being a long date with a timestamp. I now understand that the time doesn't affect it. It works fine without the format.

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i had a simalar problem and fixed it with

    WHERE (((qryMachineOutputByDate.PostingDate) Between Date()-31 And Date()))

Posting Permissions

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