Solved

Subtracting & Comparing Dates In MS Access

Posted on 2003-11-07
6
1,271 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 500 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access / SQL Server - ODBC Problems 16 53
Omit After Update event 5 43
Access 2010 Form - Turn Updating On - Off 31 94
Handle Apostrophes in SQL Parameter 16 63
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

739 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