Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Unanswered: now() is greater than 30 days.

    Hey, guys i'm trying to make a query that sorts records if they are OLDER than 30 days so "now() > 30", iv'e tried a lot of formulae but nothing has worked, and i desperately need this done with-in 24/7 hours
    -Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please show your query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    3

    Don't Know how

    i can't show my query as i'm away from the business, but it would fit in the criteria tab for "Date Done" where datedone = 1/1/07 if it has not been payed. and there is also a tab for "Date Completed"

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Phazeblade View Post
    ...it would fit in the criteria tab for "Date Done" where datedone = 1/1/07 if it has not been payed. and there is also a tab for "Date Completed"
    I'm sorry, but your post is still not really clear. If you are saying that you want your Query to pull all records where the date in the DateDone field is over thirty days in the past, you need to use the DateDiff() function, which calculates the difference between two dates, in whatever unit of time (days or "d" in this case) the developer gives.

    The Criteria for the DateDone field in the Query Grid would be

    DateDiff("d",[DateDone],Date()) > 30

    If run the Query and then re-open it in Query Grid, you will find that Access has taken the Criteria out of the DateDone field and actually
    added a calculated field to the Query! The new field's name will be

    DateDiff("d",[DateDone],Date())

    and its Criteria will now show as
    > 30

    and the Query will actuallybe run based on this new, calculated field! I only add this because it tends to confuse new developers.
    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Apr 2011
    Posts
    3
    Quote Originally Posted by Missinglinq View Post
    I'm sorry, but your post is still not really clear. If you are saying that you want your Query to pull all records where the date in the DateDone field is over thirty days in the past, you need to use the DateDiff() function, which calculates the difference between two dates, in whatever unit of time (days or "d" in this case) the developer gives.

    The Criteria for the DateDone field in the Query Grid would be

    DateDiff("d",[DateDone],Date()) > 30

    If run the Query and then re-open it in Query Grid, you will find that Access has taken the Criteria out of the DateDone field and actually
    added a calculated field to the Query! The new field's name will be

    DateDiff("d",[DateDone],Date())

    and its Criteria will now show as
    > 30

    and the Query will actuallybe run based on this new, calculated field! I only add this because it tends to confuse new developers.
    Linq ;0)>
    Hey, sorry got to work today, didn't work basically what i need if its possible is a Criteria with no user input that sorts my records by if they happened longer than 30days ago, I.E Now()>30days. iv'e searched everywhere, tried every input help function and nothing.
    And as for the query it has 8 fields
    Invoice Number
    Surname
    City
    Datedone
    Amount
    Datepaid
    Cheque
    Deposit
    Where invoice Number, is the primary key.
    And i need the query to show ONLY records of all fields that have been done more than 30days ago. WITH NO USER INPUT FIELD, this is very important.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Then, as I said
    Quote Originally Posted by Missinglinq View Post
    ...The Criteria for the DateDone field in the Query Grid would be

    DateDiff("d",[DateDone],Date()) > 30
    Your

    Now()>30days

    is saying 'If today's date and time is greater than 30 days," which is illogical! You're comparing a Date/Time (which is what Now() is) with a Number (the number of days).

    What you need to say is 'If the difference between today and the completed date is over 30 days,' then include the record.

    And that's what the Criteria I gave you does, without any input from the user.

    Also note that I have used the Date() function, rather than Now(). The Now() function should only be used if the Time is needed as well as Date. Otherwise you should use Date() which only returns the date.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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