Solved

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

Posted on 2011-02-15
6
584 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
  • 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 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

820 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