Date Subtraction in Access2007 Query, then show >30 and <60 days

Posted on 2011-02-15
Last Modified: 2013-11-27
I work in a law firm and trying to develop a MS 2007 Access query that will help us determine how long a case has been opened.  The main table has a column called "Date Opened".  Part One: In my query, I have selected "Date Opened" and then wrote the Expression of "Expr1: DateDiff("d",[Date Opened],[Now]).  When I run the query, I have to manually enter today's date, the 'Now'.  Doesn't Access already know today's date and can it run without asking me?    Part Two:  How can I get the query to only return case open dates of greater than 30 but less than 60 (but not equal to) days?   Thank you.
Question by:James0903
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34903423
try this

DateDiff("d",[Date Opened],Now())


DateDiff("d",[Date Opened],Date())
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 34903432

Field      DateDiff("d",[Date Opened],Date())

criteria Between 30 and 60

or this one

Field      DateDiff("d",[Date Opened],Date())

criteria Between 31 and 59

Author Comment

ID: 34903464
Capricorn1, thank you.  Is there a way to run query without manually inputting a date?  Thank you.
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34903478
seen my post?  what date are you referring to?

Author Comment

ID: 34903707
Yes, saw the post.  Please excuse, I was unclear.  Each time I run the query I need to input today's date, correct?  There isn't a way to just click on the query and press 'Run' without inputting a date?  Thank you.
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34903765
you don't need to enter the date, if you use the format i posted above.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question