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
  • 4
  • 2
LVL 119

Expert Comment

by:Rey Obrero
ID: 34903423
try this

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


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

Accepted Solution

Rey Obrero 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.
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Auto Filter in Combo Box 7 32
Excess tables to Excel BackUp 3 28
Restrict list data depending upon user name 3 20
tabctrl with page click event 9 0
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now