?
Solved

Subtracting & Comparing Dates In MS Access

Posted on 2003-11-07
6
Medium Priority
?
1,284 Views
Last Modified: 2008-02-01
Hello,

I would basically like to know if/how to subtract two dates in MS Access inorder to get the number of days between those dates.

Also how to compare two dates to make sure they are in the correct sequence (ie 11/12/2003 < 11/13/2003 <12/20/2004)   Please be specific as I know nothing about Access and its features.

Thanks
Moclab
0
Comment
Question by:Moclab
[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
  • 3
  • 2
6 Comments
 
LVL 11

Accepted Solution

by:
Quetzal earned 1500 total points
ID: 9705387
Subtraction:

Datediff(<interval>, <date1>, <date2>)

where <interval> is a string specifying the type of interval (hours, days, weeks, etc)...in this case "d" for days.
          <date1> less than <date2> returns the positive difference between the 2 dates

Using Abs obviates the need for compare dates:
Abs(Datediff("d", #11/12/2003#, #11/13/2003#)) = 1
Abs(Datediff("d", #11/13/2003#, #11/12/2003#)) = 1

Note: in Access we use the "#" notation to designate date constants.

If you had two database fields, Date1 and Date2, and want to calcuate the day difference in a query use:

Abs(Datediff("d",[Date1],[Date2]))
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 9705439
Regarding you second question:

Re:>Also how to compare two dates to make sure they are in the correct sequence (ie 11/12/2003 < 11/13/2003 <12/20/2004)   Please be specific as I know nothing about Access and its features.

Table1
--------------
Date1                   Date2                  Date3
11/12/2003          11/13/2003          12/20/2004



Table2
----------------
Date
11/12/2003          
11/13/2003          
12/20/2004

Do you have your data like in Table1 or Table2? If like Table2, use:

Select Date From Table2 Order By Date

You need to make a query using above SQL (to do this, start any query, add a table to it, any table, from menu, select View/SQL and replace its content with above SQL).  But first make sure filed and table names are what you have.
----------
If you have Table1, use following SQL instead:

Select Date1 As Date From Table1 Union Select All Date2 As Date From Table1 Union Select All Date3 As Date From Table1 Order By Date

Either query will sort your date fields.

Mike
0
 

Author Comment

by:Moclab
ID: 9715897
Ok, I got the dates to subtract in a query but how do you put that number back into the table.

Tbl1(date1, date2, difference)
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 34

Expert Comment

by:Mike Eghtebas
ID: 9716006
Why you want to do that?  This is not recommended.  Whenever you need the difference, vie it using just a query.  What if someone changes one of those datae in the table (without noticing the difference in days has to be changed at the same time).

Mike
0
 

Author Comment

by:Moclab
ID: 9716075
My problem is this:  I want the difference in the dates put in a difference column in the same table, so when I make a report (which prints the entire table) I can see the value of the difference in days.  Does access not automatically adjust the difference when a date is changed??
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 9716399
I will give you the code for you future use.  But for this purpose, just build a query in top of your table.  In that query add a new field with alias name called NoOfDays wehre:

NoOfDays:Datediff("d",[Date1],[Date2])         'to avoid negative number, make sure
                                                                 '  [Date1]>[Date2] or use:      

NoOfDays:Abs(Datediff("d",[Date1],[Date2]))

This should work also:

NoOfDays:[Date1]-[Date2]  
-----------------
code to add to table (not recommended):

CurrentDB.Execute "Update MyTable Set NoOfDays=[Date1]-[Date2]"

Mike

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 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