Subtracting & Comparing Dates In MS Access

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
MoclabAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QuetzalCommented:
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]))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
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
MoclabAuthor Commented:
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)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mike EghtebasDatabase and Application DeveloperCommented:
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
MoclabAuthor Commented:
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??
Mike EghtebasDatabase and Application DeveloperCommented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.