Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query to show data which is todays date - 31 days

    I am trying to produce a query which shows information which is one month less than today.
    i.e data which has a date of 8th Dec or older (I only want to include working days).
    I have in the criteria:

    [date]-31

    This does not work, any help would be appreciated.
    The data is in the format: dd-mmm-yy

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query to show data which is todays date - 31 days

    Do you want all data since a date 31 days ago except weekends?

    If so you will need to change your criteria to > Date() - 31

    I would setup a table of non weekend days over that time period and use that table in an equal join.

  3. #3
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to show data which is todays date - 31 days

    Thanks for your help.
    Is it necessary to setup this table or should this query work on its own?
    Yes l want all data to be shown for dates more than or equal to 31 working days less than today

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query to show data which is todays date - 31 days

    The problem you have is to work out the date in the past that is 31 working days back from todays date.

    Once you have worked that out that date, you can use that in your query and ignore records where the day is a Saturday or Sunday, in that way you won't need an extra table.

    Do you run this report from a form? If so, then calculate the date in the form prior to running the report.

Posting Permissions

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