Moclab
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
Tbl1(date1, date2, difference)
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
Mike
ASKER
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??
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",[Dat e1],[Date2 ]) 'to avoid negative number, make sure
' [Date1]>[Date2] or use:
NoOfDays:Abs(Datediff("d", [Date1],[D ate2]))
This should work also:
NoOfDays:[Date1]-[Date2]
-----------------
code to add to table (not recommended):
CurrentDB.Execute "Update MyTable Set NoOfDays=[Date1]-[Date2]"
Mike
NoOfDays:Datediff("d",[Dat
' [Date1]>[Date2] or use:
NoOfDays:Abs(Datediff("d",
This should work also:
NoOfDays:[Date1]-[Date2]
-----------------
code to add to table (not recommended):
CurrentDB.Execute "Update MyTable Set NoOfDays=[Date1]-[Date2]"
Mike
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