Solved

Subtracting & Comparing Dates In MS Access

Posted on 2003-11-07
6
1,220 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
  • 3
  • 2
6 Comments
 
LVL 11

Accepted Solution

by:
Quetzal earned 500 total points
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now