Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Subtracting & Comparing Dates In MS Access

Posted on 2003-11-07
6
Medium Priority
?
1,294 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

610 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