How Long Function - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

How Long Function

Emad Al-Baghli

5 years ago

Hi, I want to make database called trak meetings.
1. I have StartDate and EndDate How can I Let Access count how many years, months and days and hours left?
for example if I have meeting will start after 3 days I want access tells me the meeting will start after 2 days and 12 hours and so on...
2. I have Task has StartDate and EndDate, how can I let access tells me this task takes from you for example 3 days. I did this [EndDate]-[StartDate] I got the differences 3 but how can I let the access say for example 3 years and 3 months and 15 days

Thanks!

Richard Rost @Reply

5 years ago

I wrote a function called HowLong. You can use it for exactly this purpose. You can send it a DateTime value and it will return a string telling how long ago it was or how long until it occurs. With a little modification you could easily send it a 2nd date to get a difference between them. My function just uses Now().

It's available in the Code Vault for Gold Members.

Public Function HowLong(T As Date, Optional Exact As Boolean = False, _

            Optional Accuracy As String = "ymdhns", Optional InAgo As Boolean = True, _

            Optional Abbrev As Boolean = False) As String

Usage assuming today's date is 8/23/2020
(min and sec are advancing as I'm typing this, but you get the point):

HowLong(#8/1/2018#)                           2 years ago
HowLong(#8/1/2020#)                           22 days ago
HowLong(#8/22/2020 10pm#)                     17 hours ago
HowLong(#8/22/2020 10pm#, True)               17 hours, 30 minutes, 23 seconds ago
HowLong(#8/22/2020 10pm#, True, "hn")         17 hours, 30 minutes ago
HowLong(#8/1/2018#, True)                     2 years, 22 days, 15 hours, 39 minutes, 14 seconds ago
HowLong(#8/1/2018#, True, "ymdhn")            2 years, 22 days, 15 hours, 39 minutes ago
HowLong(#8/1/2018#, True, "ymdhn", False)     2 years, 22 days, 15 hours, 39 minutes
HowLong(#6/1/2018#, True, "ym", False, True)  2 yr, 2 mo
HowLong(#1/1/2010#, False, "mmdd")            127 months ago
HowLong(#1/1/2010#, True, "mmdd")             127 months, 22 days ago
HowLong(#1/1/2025#)                           in 4 years
HowLong(#1/1/2025#, True, "ymdh")             in 4 years, 4 months, 8 days, 8 hours
HowLong(#1/1/2021#, True, "hn", True, True)   in 3128 hr, 9 min

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View