Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Calculating on a date/time field
- Thread starter goslincm
- Start date
no, you should capture them as datetime, you can then use the DateDiff function to return the elapsed time formatted as you require.
Leslie
Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual
Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi
Leslie
Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual
Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi
- Thread starter
- #3
A quick search of Access Help for DateDiff returns:
[tt] Calculate the difference between two date or time values
The following table lists examples of expressions that use the DateDiff function to calculate the difference between any two date and time values.
You can use these expressions in calculated controls (calculated control: A control that is used on a form, report, or data access page to display the result of an expression. The result is recalculated each time there is a change in any of the values on which the expression is based.) on forms, reports, and data access pages (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.).
Expression Description
=DateDiff("d", [OrderDate], [ShippedDate]) Displays the variance in days between the values of the OrderDate and ShippedDate fields.
=DateDiff("yyyy", [Birthday1], [Birthday2]) Displays the variance in years between the values of the Birthday1 and Birthday2 fields.
=DateDiff("m", #12/24/2000#, #11/26/2000#) Displays the variance in months between the two dates. The expression evaluates to -1, since the first date falls after the second date.
=DateDiff("yyyy", #12/31/2000#, #1/1/2001#) Displays the variance in years between the two dates. The expression evaluates to 1, even though only a day has elapsed.
You can use these expressions in a calculated field in a query.
Expression Description
LagTime: DateDiff("d", [OrderDate], [ShippedDate]) Displays in the LagTime field the number of days between the values of the OrderDate and ShippedDate fields.
YearsLapsed: DateDiff("yyyy", #12/31/2000#, #1/1/2001#) Displays in the YearsLapsed field the number of years between the two dates.
[/tt]
It also includes the "See Also" section with the following topics:
Add to or subtract from date and time values
Calculate the serial number of the day, week, or month in a date
Combine text, number, date, and time values
Extract a part of a date or time value
Format date and time values
Use current date and time in calculations
[tt] Calculate the difference between two date or time values
The following table lists examples of expressions that use the DateDiff function to calculate the difference between any two date and time values.
You can use these expressions in calculated controls (calculated control: A control that is used on a form, report, or data access page to display the result of an expression. The result is recalculated each time there is a change in any of the values on which the expression is based.) on forms, reports, and data access pages (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.).
Expression Description
=DateDiff("d", [OrderDate], [ShippedDate]) Displays the variance in days between the values of the OrderDate and ShippedDate fields.
=DateDiff("yyyy", [Birthday1], [Birthday2]) Displays the variance in years between the values of the Birthday1 and Birthday2 fields.
=DateDiff("m", #12/24/2000#, #11/26/2000#) Displays the variance in months between the two dates. The expression evaluates to -1, since the first date falls after the second date.
=DateDiff("yyyy", #12/31/2000#, #1/1/2001#) Displays the variance in years between the two dates. The expression evaluates to 1, even though only a day has elapsed.
You can use these expressions in a calculated field in a query.
Expression Description
LagTime: DateDiff("d", [OrderDate], [ShippedDate]) Displays in the LagTime field the number of days between the values of the OrderDate and ShippedDate fields.
YearsLapsed: DateDiff("yyyy", #12/31/2000#, #1/1/2001#) Displays in the YearsLapsed field the number of years between the two dates.
[/tt]
It also includes the "See Also" section with the following topics:
Add to or subtract from date and time values
Calculate the serial number of the day, week, or month in a date
Combine text, number, date, and time values
Extract a part of a date or time value
Format date and time values
Use current date and time in calculations
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question