Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-15
6
Medium Priority
?
590 Views
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.
0
Comment
Question by:James0903
[X]
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
6 Comments
 
LVL 120

Expert Comment

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


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

or


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

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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
0
 

Author Comment

by:James0903
ID: 34903464
Capricorn1, thank you.  Is there a way to run query without manually inputting a date?  Thank you.
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 120

Expert Comment

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

Author Comment

by:James0903
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.
0
 
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.
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

670 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