Results 1 to 6 of 6
  1. #1
    MsAdams is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2012
    Posts
    8

    Query Criteria to show only records with date values between Today -5 and Today -30

    I am trying to structure the query criteria statement to limit query results to only show records where the date values in a date/time field in the query (DateFlagged) are between Date()- 5days a Date()-30 days. I don't want to have to hard-code or prompt for specific dates each time the query is run. Ultimately I will be using this query to generate a report.

    I have tried several strings to get this to work - Here's what I have tried so far:

    Between Date()-5 And Date()-30

    Between DateAdd("d",-5,Date()) and DateAdd("d",-30,Date())

    >Date()-5 and <= Date()-30

    None of them return the results I expect. I am sure it is something very simple and I am just overthinking this....



    Any help would be appreciated!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try reversing them. As a rule, you want the smaller value first in a Between clause. Your > test is also reversed. No date could be greater than 5 days ago and less than 30 days ago.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    MsAdams is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2012
    Posts
    8
    I have tried reversing them (and I feel like a moron for not thinking through what the strings were actually saying....)

    <=Date()-5 And >=Date()-30

    Between Date()-30 And Date()-5

    I am still not getting the expected results. The dates for my sample data are definitely in range of the criteria. Am I totally missing something here?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That's the criteria on a date field? What are you getting?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    MsAdams is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2012
    Posts
    8
    I am not getting any results when I have a sample record with the DateFlagged = 09/27/2012 (which should be within the range).

    I decided to go about this a different way for now.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If there's a time element, you'd need to account for it. One way is the DateValue function:

    WHERE DateValue(DateTimeFieldName) Between...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-24-2012, 02:34 PM
  2. Replies: 4
    Last Post: 03-23-2012, 01:18 PM
  3. Show report for today only
    By cooper in forum Reports
    Replies: 6
    Last Post: 09-06-2011, 12:46 PM
  4. Entering Today's Date
    By Alex Motilal in forum Access
    Replies: 5
    Last Post: 08-23-2011, 12:22 AM
  5. query date that is five years older than today
    By cpsummer in forum Queries
    Replies: 2
    Last Post: 09-26-2007, 02:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums