Overflow Runtime error '6'

[Guest]

I running an update query and getting an overflow error.
I have run this before and no problems just this time. Any ideas on
how i can correct it. Thanks


Function TimeConv(dteStart, dteEnd As Date) As Double

Dim X As Integer, Y As Integer, I As Integer

X = 0
Y = 1
I = 0

X = Int(dteEnd - dteStart + 0.5) ( Debug error(this is the error)

If X > 0 Then
Do Until Y = X + 1
If WeekDay(dteStart + Y) = 1 Or WeekDay(dteStart + Y) = 7 Then
I = I + 1
End If
Y = Y + 1
Loop
TimeConv = ((Abs(dteStart - dteEnd) * 86400) - (I * 86400)) / 3600
Else
TimeConv = Abs(dteStart - dteEnd) * 86400 / 3600
End If

If TimeConv < 0 Then
TimeConv = 1
End If


End Function

 

[Guest]

It has run fine until now because it varies depending on the start and end
date.
What is the start and end date that it overflows on bearing in mind that you
have declared X as an integer which means the biggest value it can hold is
32767 ?
If the answer to your calculation is larger than this, then this is the
overflow.
Try declaring X, Y & I as Long

 

[JohnFol]

Dates are stored as floating point numbers however the Int function takes a
double as a parameter. It may be the result of the maths cannot be
implicitly cast to a double

 

[Wayne Morgan]

Function TimeConv(dteStart, dteEnd As Date) As Double

dteStart is a Variant and depending on the format the date is in when
passed, you may get very undesirable results. For example, Access may
correctly interpret 1 Jan 2005 as a date, but may interpret 1/1/2005 as a
division problem. The result of this division would give dteStart a value of
4.98753117206983E-04. The above line should be:

Function TimeConv(dteStart As Date, dteEnd As Date) As Double