Days / Hours / Minutes / Seconds - Calculation Help Needed Please

[Tbird2340]

I have an Access table that has a field named "DateSubmitted".. The default
value of this field is Now() which will insert the date / time when a new
record is created.

I want to know how old each record is so I have a query with the following
field:

HowOld: Now()-[DateSubmitted]

This just gives me the results in decimal format.

IE: 0.08 or 6.41, etc..

I want to format this as X day(s), X hour(s), X minute(s), X seconds.

How do I do this?

Thanks in advance!

 

[Brendan Reynolds]

I haven't done any very intensive testing of this, but I believe it should
work ...

Public Function DaysHoursMinutesSeconds(ByVal StartDate As Date, ByVal
EndDate As Date) As String

Dim lngSeconds As Long
Dim lngMinutes As Long
Dim lngHours As Long
Dim lngDays As Long
Dim strResult As String

lngSeconds = DateDiff("s", StartDate, EndDate)
lngMinutes = lngSeconds \ 60
lngSeconds = lngSeconds Mod 60
lngHours = lngMinutes \ 60
lngMinutes = lngMinutes Mod 60
lngDays = lngHours \ 24
lngHours = lngHours Mod 24

strResult = Format$(lngDays, "00") & ":" & Format$(lngHours, "00") & ":"
& _
Format$(lngMinutes, "00") & ":" & Format$(lngSeconds, "00")

DaysHoursMinutesSeconds = strResult

End Function

A quick test in the Immediate window ...
? dayshoursminutesseconds(#2 feb 2005 17:30:30#, #3 feb 2005 18:35:35#)
01:01:05:05

 

[Andi Mayer]

the same in a shorter version

Function DaysHoursMinutesSeconds(ByVal StartDate As Date, _
ByVal EndDate As Date) As String
Dim aDate As Date
aDate = CDate(EndDate - StartDate)
DaysHoursMinutesSeconds = DateDiff("d", #12:00:00 AM#, aDate) _
& ":" & Format(aDate, "Long Time")
End Function

 

[Tbird2340]

Brendan, thanks for the reply..

So do I create a module with that code in it? What do I do after that? Sorry
but I never used modules before...

Thanks

 

[Douglas J. Steele]

You may want to take a look at the function Graham Seach & I wrote.
http://members.rogers.com/douglas.j.steele/Diff2Dates.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I haven't done any very intensive testing of this, but I believe it should
work ...

Public Function DaysHoursMinutesSeconds(ByVal StartDate As Date, ByVal
EndDate As Date) As String

Dim lngSeconds As Long
Dim lngMinutes As Long
Dim lngHours As Long
Dim lngDays As Long
Dim strResult As String

lngSeconds = DateDiff("s", StartDate, EndDate)
lngMinutes = lngSeconds \ 60
lngSeconds = lngSeconds Mod 60
lngHours = lngMinutes \ 60
lngMinutes = lngMinutes Mod 60
lngDays = lngHours \ 24
lngHours = lngHours Mod 24

strResult = Format$(lngDays, "00") & ":" & Format$(lngHours, "00") &
":" & _
Format$(lngMinutes, "00") & ":" & Format$(lngSeconds, "00")

DaysHoursMinutesSeconds = strResult

End Function

A quick test in the Immediate window ...
? dayshoursminutesseconds(#2 feb 2005 17:30:30#, #3 feb 2005 18:35:35#)
01:01:05:05

--
Brendan Reynolds (MVP)

I have an Access table that has a field named "DateSubmitted".. The
default value of this field is Now() which will insert the date / time
when a new record is created.

I want to know how old each record is so I have a query with the
following field:

HowOld: Now()-[DateSubmitted]

This just gives me the results in decimal format.

IE: 0.08 or 6.41, etc..

I want to format this as X day(s), X hour(s), X minute(s), X seconds.

How do I do this?

Thanks in advance!

 

[Tbird2340]

I really appreciate all the replies... Would it be too much for someone to
tell me how to implement this? Do I create a module? Do I get the values I
want by adding the module to the query? Sorry for the ignorance I just
really need to get this working.

Thanks all...

 

[Brendan Reynolds]

Yes, paste the code into a module. The module is just a container, though,
you don't refer to the module in the query, just the name of the function
followed by the arguments to the function in parentheses. Something like ...

HowOld: DaysHoursMinutesSeconds(Now(), [DateSubmitted])

 

[SirPoonga]

Adn this is one of hte powerful features of aspect. I sometimes forget
you can run functions inside of queries since I come from a MySQL/SQL
Server background.

To reiterate what was siad though. You create a module. It's just a
place to store code. You use the function names directly like Brendan
Reynolds said.

Like recently I needed to create an update query that took a bunch of
part numbers and remove the dashes. The part numbers contained an 'A'
at the beginning, 5 numbers afterwords, a dash, some amount of numbers,
a dash, and another amount of numbers. I had to create a
replace_partnum function that used VBA's replace to do what I wanted to
do.

It is a powerful featue that I know many people overlook.

 

[Tbird2340]

Ok.. I created a new module and pasted the following code in it:

Function DaysHoursMinutesSeconds(ByVal StartDate As Date, _
ByVal EndDate As Date) As String
Dim aDate As Date
aDate = CDate(EndDate - StartDate)
DaysHoursMinutesSeconds = DateDiff("d", #12:00:00 AM#, aDate) _
& ":" & Format(aDate, "Long Time")
End Function

Then I saved it and went into my query and pasted the following in the
field:

HowOld: DaysHoursMinutesSeconds(Now(), [DateSubmitted])

When I try to run the query I get the error:

"Ambiguous name. in query expression
'DaysHoursMinutesSeconds(Now(),[DateSubmitted])'.

Any ideas?

 

[SirPoonga]

Did you include the right table(s)?

 

[Tbird2340]

Ok.. In my query I have added the appropriate table.

In the "Field:" field I HAD HowOld: Date()-[DateSubmitted] with nothing in
the "Table:" field under it and it worked...

However, when I removed that and entered the:

"HowOld: DaysHoursMinutesSeconds(Now(), [DateSubmitted])"

with nothing in the "Table:" field I got the error I posted.. Then I put the
table name in the "Table:" field under it and now I get the error:

"Extra ) in query expression
'HelpDesk.[DaysHoursMinutesSeconds(Now(),[DateSubmitted])]'."

 

[Tbird2340]

That is indeed what was wrong... However, I get the following results:
"6:7:52:32 PM"

Is there anyway for it to display as:

"6 days, 7 hours, 52 minutes, and 32 seconds old"?

Hi Tbird,

This could be because there is a duplicate DaysHoursMinutesSeconds
function.
Make sure you only have one DaysHoursMinutesSeconds function in all your
modules.

Immanuel Sibero

Ok.. I created a new module and pasted the following code in it:

Function DaysHoursMinutesSeconds(ByVal StartDate As Date, _
ByVal EndDate As Date) As String
Dim aDate As Date
aDate = CDate(EndDate - StartDate)
DaysHoursMinutesSeconds = DateDiff("d", #12:00:00 AM#, aDate) _
& ":" & Format(aDate, "Long Time")
End Function

Then I saved it and went into my query and pasted the following in the
field:

HowOld: DaysHoursMinutesSeconds(Now(), [DateSubmitted])

When I try to run the query I get the error:

"Ambiguous name. in query expression
'DaysHoursMinutesSeconds(Now(),[DateSubmitted])'.

Any ideas?

 

[Immanuel Sibero]

Hi Tbird,

This could be because there is a duplicate DaysHoursMinutesSeconds function.
Make sure you only have one DaysHoursMinutesSeconds function in all your
modules.

Immanuel Sibero

 

[SirPoonga]

change the Format function in the DaysHoursMinutesSeconds to something
like Format(aDate, "d days, h hours, m minutes, s seconds"). unless
someone else replies you will need to consult the VBA help to find out
the exact format string you need.

 

[Tbird2340]

Also, I'm trying to display this query in an ASP page and I'm having
problems.. I was able to view it before I created the function call...

I'm getting the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function
'DaysHoursMinutesSeconds' in expression.

/intranet/Admin/HelpRequest.asp, line 27

I understand that this is an Access message board and not an ASP so I
understand if no one knows the answer but I figured it couldn't hurt.

Thanks for all the help.

 

[Brendan Reynolds]

You really needed to tell us that a lot sooner. You can't use custom VBA
functions in Jet queries executed outside of the Microsoft Access
environment. Unless someone can think of a way of doing it using only
built-in functions, you'll have to retrieve the data 'as is' and process it
in the ASP code instead of in the query.

 

[Tbird2340]

Crap... I'm sorry.. I didn't even think about it..
So does anyone know how to process it in the asp code?

Sorry again...

 

[SirPoonga]

ASP is alot like VB and VBA. There probably is a Format function that
you could use. Or a DateFormat. Something of that nature is what you
need to search for.

 

[Gregory Paret]

I have an Access table that has a field named "DateSubmitted".. The default
value of this field is Now() which will insert the date / time when a new
record is created.

I want to know how old each record is so I have a query with the following
field:

HowOld: Now()-[DateSubmitted]

This just gives me the results in decimal format.

IE: 0.08 or 6.41, etc..

I want to format this as X day(s), X hour(s), X minute(s), X seconds.

How do I do this?

Thanks in advance!

HowOld: format(int(Now()-[DateSubmitted]), "0 day(s)") &
format(Now()-[DateSubmitted],
", h \h\o\u\r(\s), n \m\i\n\u\t\e(\s), s \s\e\c\o\n\d\s.")

Unless you need to see the string in your query result, you could leave your
query expression as-is and put the format stuff in the control source(s) in
your form or report.

-Greg.

 

[Douglas J. Steele]

ASP is nothing like VB or VBA. VBScript, one of the languages you can use
with ASP, is a lot like VB or VBA.