Elapsed Time

J

jo

I had copied the ElapsedTime function that I found in the
Knowledge base of Access. It works fine with the military
time when I test it in the debug window BUT..
I get Conversion errors when I place that function as an
update query. Example: StartDateTime06/13/2004 09:16:00
EndDateTime06/14/2004 17:03:00
with the function in the update field called Total
Time.
I tried placing all fields at Date/Time format, I got an
conversion error. Then I switched them all to text and I
still got an error.

What format should the fields be in for me to get just the
number.
 
G

Guest

ElapsedTime([TimeLog]![EndDateTime]-[TimeLog]!
[StartDateTime]) is placed in the 'update' row of the
query.

And this is the module
Function ElapsedTime(Interval)
Dim x
x = Int(CSng(Interval * 24 * 60))

End Function

Thanks for the help
 
K

Ken Snell [MVP]

Is TimeLog the name of a table? If yes, use . not !

ElapsedTime([TimeLog].[EndDateTime]-[TimeLog].[StartDateTime])


--

Ken Snell
<MS ACCESS MVP>

ElapsedTime([TimeLog]![EndDateTime]-[TimeLog]!
[StartDateTime]) is placed in the 'update' row of the
query.

And this is the module
Function ElapsedTime(Interval)
Dim x
x = Int(CSng(Interval * 24 * 60))

End Function

Thanks for the help
-----Original Message-----
Post the query that you tried to use and got the error with....

--

Ken Snell
<MS ACCESS MVP>




.
Click to expand...
Click to expand...
 
J

jo

I get a type conversion error.
-----Original Message-----
Is TimeLog the name of a table? If yes, use . not !

ElapsedTime([TimeLog].[EndDateTime]-[TimeLog]. [StartDateTime])


--

Ken Snell
<MS ACCESS MVP>

ElapsedTime([TimeLog]![EndDateTime]-[TimeLog]!
[StartDateTime]) is placed in the 'update' row of the
query.

And this is the module
Function ElapsedTime(Interval)
Dim x
x = Int(CSng(Interval * 24 * 60))

End Function

Thanks for the help
-----Original Message-----
Post the query that you tried to use and got the error with....

--

Ken Snell
<MS ACCESS MVP>

I had copied the ElapsedTime function that I found in the
Knowledge base of Access. It works fine with the military
time when I test it in the debug window BUT..
I get Conversion errors when I place that function as an
update query. Example: StartDateTime06/13/2004 09:16:00
EndDateTime06/14/2004 17:03:00
with the function in the update field called Total
Time.
I tried placing all fields at Date/Time format, I got an
conversion error. Then I switched them all to text and I
still got an error.

What format should the fields be in for me to get
Click to expand...
just
the
number.


.
Click to expand...
Click to expand...


.
Click to expand...
 
K

Ken Snell [MVP]

What is the field type of the EndDateTime and the StartDateTime fields?
Date/Time? Text? Number?


Also, though not causing the error you're seeing (at least, I don't believe
it is), the function needs a slight change:

Function ElapsedTime(Interval)
Dim x
x = Int(CSng(Interval * 24 * 60))
ElapsedTime = x
End Function


--

Ken Snell
<MS ACCESS MVP>


jo said:
I get a type conversion error.
-----Original Message-----
Is TimeLog the name of a table? If yes, use . not !

ElapsedTime([TimeLog].[EndDateTime]-[TimeLog]. [StartDateTime])


--

Ken Snell
<MS ACCESS MVP>

ElapsedTime([TimeLog]![EndDateTime]-[TimeLog]!
[StartDateTime]) is placed in the 'update' row of the
query.

And this is the module
Function ElapsedTime(Interval)
Dim x
x = Int(CSng(Interval * 24 * 60))

End Function

Thanks for the help
-----Original Message-----
Post the query that you tried to use and got the error
with....

--

Ken Snell
<MS ACCESS MVP>

message
I had copied the ElapsedTime function that I found in
the
Knowledge base of Access. It works fine with the
military
time when I test it in the debug window BUT..
I get Conversion errors when I place that function as an
update query. Example: StartDateTime06/13/2004 09:16:00
EndDateTime06/14/2004 17:03:00
with the function in the update field called Total
Time.
I tried placing all fields at Date/Time format, I got an
conversion error. Then I switched them all to text and I
still got an error.

What format should the fields be in for me to get just
the
number.


.
Click to expand...


.
Click to expand...
Click to expand...
 
P

Paul Overway

Yes....as you should. Date is a double....you're trying to convert a date
data type to a Single and then an Integer after multiplying it by 24 and 60.
Any interval greater than 65535 minutes will cause the error. You might try
using CLng, Int, or Fix instead, i.e.,

Int((Abs([TimeLog].[EndDateTime]-[TimeLog].[StartDateTime])) * 24 * 60)

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


jo said:
I get a type conversion error.
-----Original Message-----
Is TimeLog the name of a table? If yes, use . not !

ElapsedTime([TimeLog].[EndDateTime]-[TimeLog]. [StartDateTime])> >

--

Ken Snell
<MS ACCESS MVP>

ElapsedTime([TimeLog]![EndDateTime]-[TimeLog]!
[StartDateTime]) is placed in the 'update' row of the
query.

And this is the module
Function ElapsedTime(Interval)
Dim x
x = Int(CSng(Interval * 24 * 60))

End Function

Thanks for the help
-----Original Message-----
Post the query that you tried to use and got the error
with....

--

Ken Snell
<MS ACCESS MVP>

message
I had copied the ElapsedTime function that I found in
the
Knowledge base of Access. It works fine with the
military
time when I test it in the debug window BUT..
I get Conversion errors when I place that function as an
update query. Example: StartDateTime06/13/2004 09:16:00
EndDateTime06/14/2004 17:03:00
with the function in the update field called Total
Time.
I tried placing all fields at Date/Time format, I got an
conversion error. Then I switched them all to text and I
still got an error.

What format should the fields be in for me to get just
the
number.


.
Click to expand...


.
Click to expand...
Click to expand...
 
G

Guest

I had originally had t used Date/Time for EndDate and
StartDate with TotTime as a number. I had got a
conversion error message. Then I placed all three fields
as Date/time again an error and finally text and that is
where it is at now.
-----Original Message-----
What is the field type of the EndDateTime and the StartDateTime fields?
Date/Time? Text? Number?


Also, though not causing the error you're seeing (at least, I don't believe
it is), the function needs a slight change:

Function ElapsedTime(Interval)
Dim x
x = Int(CSng(Interval * 24 * 60))
ElapsedTime = x
End Function


--

Ken Snell
<MS ACCESS MVP>


I get a type conversion error.
-----Original Message-----
Is TimeLog the name of a table? If yes, use . not !

ElapsedTime([TimeLog].[EndDateTime]-[TimeLog]. [StartDateTime])


--

Ken Snell
<MS ACCESS MVP>

ElapsedTime([TimeLog]![EndDateTime]-[TimeLog]!
[StartDateTime]) is placed in the 'update' row of the
query.

And this is the module
Function ElapsedTime(Interval)
Dim x
x = Int(CSng(Interval * 24 * 60))

End Function

Thanks for the help
-----Original Message-----
Post the query that you tried to use and got the error
with....

--

Ken Snell
<MS ACCESS MVP>

message
I had copied the ElapsedTime function that I found in
the
Knowledge base of Access. It works fine with the
military
time when I test it in the debug window BUT..
I get Conversion errors when I place that function as an
update query. Example: StartDateTime06/13/2004 09:16:00
EndDateTime06/14/2004 17:03:00
with the function in the update field called Total
Time.
I tried placing all fields at Date/Time format, I got an
conversion error. Then I switched them all to text and I
still got an error.

What format should the fields be in for me to get just
the
number.


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
G

Guest

Where am I suppose to put that procedure, in the module or
in the update query? I Put it in the module and still
type conversion error.
-----Original Message-----
Yes....as you should. Date is a double....you're trying to convert a date
data type to a Single and then an Integer after multiplying it by 24 and 60.
Any interval greater than 65535 minutes will cause the error. You might try
using CLng, Int, or Fix instead, i.e.,

Int((Abs([TimeLog].[EndDateTime]-[TimeLog]. [StartDateTime])) * 24 * 60)

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


I get a type conversion error.
-----Original Message-----
Is TimeLog the name of a table? If yes, use . not !

ElapsedTime([TimeLog].[EndDateTime]-[TimeLog]. [StartDateTime])> >

--

Ken Snell
<MS ACCESS MVP>

ElapsedTime([TimeLog]![EndDateTime]-[TimeLog]!
[StartDateTime]) is placed in the 'update' row of the
query.

And this is the module
Function ElapsedTime(Interval)
Dim x
x = Int(CSng(Interval * 24 * 60))

End Function

Thanks for the help
-----Original Message-----
Post the query that you tried to use and got the error
with....

--

Ken Snell
<MS ACCESS MVP>

message
I had copied the ElapsedTime function that I found in
the
Knowledge base of Access. It works fine with the
military
time when I test it in the debug window BUT..
I get Conversion errors when I place that function as an
update query. Example: StartDateTime06/13/2004 09:16:00
EndDateTime06/14/2004 17:03:00
with the function in the update field called Total
Time.
I tried placing all fields at Date/Time format, I got an
conversion error. Then I switched them all to text and I
still got an error.

What format should the fields be in for me to get just
the
number.


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
P

Paul Overway

You'll also get an error if either of the fields are null...check your data.
The statement provided can go in your query...if neither field contains
nulls.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Where am I suppose to put that procedure, in the module or
in the update query? I Put it in the module and still
type conversion error.
-----Original Message-----
Yes....as you should. Date is a double....you're trying to convert a date
data type to a Single and then an Integer after multiplying it by 24 and 60.
Any interval greater than 65535 minutes will cause the error. You might try
using CLng, Int, or Fix instead, i.e.,

Int((Abs([TimeLog].[EndDateTime]-[TimeLog]. [StartDateTime])) * 24 * 60)

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


I get a type conversion error.
-----Original Message-----
Is TimeLog the name of a table? If yes, use . not !

ElapsedTime([TimeLog].[EndDateTime]-[TimeLog].
[StartDateTime])> >

--

Ken Snell
<MS ACCESS MVP>

ElapsedTime([TimeLog]![EndDateTime]-[TimeLog]!
[StartDateTime]) is placed in the 'update' row of the
query.

And this is the module
Function ElapsedTime(Interval)
Dim x
x = Int(CSng(Interval * 24 * 60))

End Function

Thanks for the help
-----Original Message-----
Post the query that you tried to use and got the error
with....

--

Ken Snell
<MS ACCESS MVP>

message
I had copied the ElapsedTime function that I found in
the
Knowledge base of Access. It works fine with the
military
time when I test it in the debug window BUT..
I get Conversion errors when I place that function
as an
update query. Example: StartDateTime06/13/2004
09:16:00
EndDateTime06/14/2004 17:03:00
with the function in the update field called Total
Time.
I tried placing all fields at Date/Time format, I
got an
conversion error. Then I switched them all to text
and I
still got an error.

What format should the fields be in for me to get
just
the
number.


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
P

Paul Overway

BTW...the fields should be date data type.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Where am I suppose to put that procedure, in the module or
in the update query? I Put it in the module and still
type conversion error.
-----Original Message-----
Yes....as you should. Date is a double....you're trying to convert a date
data type to a Single and then an Integer after multiplying it by 24 and 60.
Any interval greater than 65535 minutes will cause the error. You might try
using CLng, Int, or Fix instead, i.e.,

Int((Abs([TimeLog].[EndDateTime]-[TimeLog]. [StartDateTime])) * 24 * 60)

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


I get a type conversion error.
-----Original Message-----
Is TimeLog the name of a table? If yes, use . not !

ElapsedTime([TimeLog].[EndDateTime]-[TimeLog].
[StartDateTime])> >

--

Ken Snell
<MS ACCESS MVP>

ElapsedTime([TimeLog]![EndDateTime]-[TimeLog]!
[StartDateTime]) is placed in the 'update' row of the
query.

And this is the module
Function ElapsedTime(Interval)
Dim x
x = Int(CSng(Interval * 24 * 60))

End Function

Thanks for the help
-----Original Message-----
Post the query that you tried to use and got the error
with....

--

Ken Snell
<MS ACCESS MVP>

message
I had copied the ElapsedTime function that I found in
the
Knowledge base of Access. It works fine with the
military
time when I test it in the debug window BUT..
I get Conversion errors when I place that function
as an
update query. Example: StartDateTime06/13/2004
09:16:00
EndDateTime06/14/2004 17:03:00
with the function in the update field called Total
Time.
I tried placing all fields at Date/Time format, I
got an
conversion error. Then I switched them all to text
and I
still got an error.

What format should the fields be in for me to get
just
the
number.


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

It appears that you want to get the elapsed seconds. Any particular reason
you can't just use the DateDiff function:

DateDiff("s", StartDateTime, EndDateTime)

The StartDateTime and EndDateTime fields should be Date/Time. The TotTime
field should be Long Integer.
--

Ken Snell
<MS ACCESS MVP>

I had originally had t used Date/Time for EndDate and
StartDate with TotTime as a number. I had got a
conversion error message. Then I placed all three fields
as Date/time again an error and finally text and that is
where it is at now.
-----Original Message-----
What is the field type of the EndDateTime and the StartDateTime fields?
Date/Time? Text? Number?


Also, though not causing the error you're seeing (at least, I don't believe
it is), the function needs a slight change:

Function ElapsedTime(Interval)
Dim x
x = Int(CSng(Interval * 24 * 60))
ElapsedTime = x
End Function


--

Ken Snell
<MS ACCESS MVP>


I get a type conversion error.
-----Original Message-----
Is TimeLog the name of a table? If yes, use . not !

ElapsedTime([TimeLog].[EndDateTime]-[TimeLog].
[StartDateTime])


--

Ken Snell
<MS ACCESS MVP>

ElapsedTime([TimeLog]![EndDateTime]-[TimeLog]!
[StartDateTime]) is placed in the 'update' row of the
query.

And this is the module
Function ElapsedTime(Interval)
Dim x
x = Int(CSng(Interval * 24 * 60))

End Function

Thanks for the help
-----Original Message-----
Post the query that you tried to use and got the error
with....

--

Ken Snell
<MS ACCESS MVP>

message
I had copied the ElapsedTime function that I found in
the
Knowledge base of Access. It works fine with the
military
time when I test it in the debug window BUT..
I get Conversion errors when I place that function
as an
update query. Example: StartDateTime06/13/2004
09:16:00
EndDateTime06/14/2004 17:03:00
with the function in the update field called Total
Time.
I tried placing all fields at Date/Time format, I
got an
conversion error. Then I switched them all to text
and I
still got an error.

What format should the fields be in for me to get
just
the
number.


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
J

jo

THANK YOU, THank you. The DateDiff function as well as
the correct data type did the trick.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

compute elapsed time 3
Calculate elapsed time excluding specific timeframe 10
Dcount past time 2
Automation error in report 4
Access Access - Elapsed Time between Records 5
Why can't Access find my table? 2
separating date and time 5
Calculating Elapsed Time 3

Top