Overflow Runtime error '6'

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lichase - 26 May 2005 13:31 GMT

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

Reply to this Message

Dennis - 26 May 2005 13:55 GMT

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

> I running an update query and getting an overflow error.
> I have run this before and no problems just this time. Any ideas on
[quoted text clipped - 27 lines]
>
> End Function

Reply to this Message

JohnFol - 26 May 2005 13:59 GMT

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

> I running an update query and getting an overflow error.
> I have run this before and no problems just this time. Any ideas on
[quoted text clipped - 27 lines]
>
> End Function

Reply to this Message

Wayne Morgan - 26 May 2005 13:59 GMT

> 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

Signature

Wayne Morgan
MS Access MVP

> I running an update query and getting an overflow error.
> I have run this before and no problems just this time. Any ideas on
[quoted text clipped - 27 lines]
>
> End Function

Reply to this Message