Go Premium for a chance to win a PS4. Enter to Win

x
Solved

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

Posted on 2011-02-15
Medium Priority
591 Views
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
Question by:James0903
• 4
• 2

LVL 120

Expert Comment

ID: 34903423
try this

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

or

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

LVL 120

Accepted Solution

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

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

LVL 120

Expert Comment

ID: 34903478
seen my post?  what date are you referring to?
0

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.
0

LVL 120

Expert Comment

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

## Featured Post

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
###### Suggested Courses
Course of the Month6 days, 10 hours left to enroll

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

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