CODEWITHSUNDEEP
vbams-access
NavyNuke
NavyNuke

Reputation: 19

Why Am I getting Overflow Error????? VBA/ ACCESS

Please help me identify what I am doing wrong. I am getting an overflow error with the section "weekendDays = weekenddays + 1 " highlighted. The purpose of this code is to calculate the number of Tuesdays, Thursdays, Saturdays, and Sundays in 2 date ranges.

  '//////This is for Valley Estimate of Demurrage Days/////////////
Public Function ModWeekdays(ByRef NotificationDate As Date, ByRef OrderDate As Date, ByRef PlacementDate As Date, ByRef ReleaseDate As Date) As Integer
Dim skips As Integer
Dim WeekendDays As Integer
Dim WeekendDays2 As Integer
'skips = 0
WeekendDays = 0
WeekendDays2 = 0


   Do
   If DatePart("w", NotificationDate) = 0 Then
   WeekendDays = WeekendDays + 1
   ElseIf DatePart("w", NotificationDate) = 2 Then
   WeekendDays = WeekendDays + 1
   ElseIf DatePart("w", NotificationDate) = 4 Then
   WeekendDays = WeekendDays + 1
   ElseIf DatePart("w", NotificationDate) = 6 Then
   WeekendDays = WeekendDays + 1
   End If
   Loop Until NotificationDate = OrderDate

   Do
   If DatePart("w", PlacementDate) = 0 Then
   WeekendDays2 = WeekendDays2 + 1
   ElseIf DatePart("w", PlacementDate) = 2 Then
   WeekendDays2 = WeekendDays2 + 1
   ElseIf DatePart("w", PlacementDate) = 4 Then
   WeekendDays2 = WeekendDays2 + 1
   ElseIf DatePart("w", PlacementDate) = 6 Then
   WeekendDays2 = WeekendDays + 1
   End If
   Loop Until PlacementDate = ReleaseDate

   skips = WeekendDays + WeekendDays2
   skips = ModWeekdays(NotificationDate, OrderDate, PlacementDate, ReleaseDate)


End Function

Upvotes: 0

Views: 736

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8402

You aren't incrementing your variable, so you're in an infinite loop. This should fix some of the back-and-forth from our comments.

Your function should look like this:

'//////This is for Valley Estimate of Demurrage Days/////////////
Public Function ModWeekdays(ByRef NotificationDate As Date, ByRef OrderDate As Date, ByRef PlacementDate As Date, ByRef ReleaseDate As Date) As Integer
Dim skips As Integer
Dim WeekendDays As Integer
Dim WeekendDays2 As Integer
'skips = 0
WeekendDays = 0
WeekendDays2 = 0


   Do
   If DatePart("w", NotificationDate) = 0 Then
   WeekendDays = WeekendDays + 1
   ElseIf DatePart("w", NotificationDate) = 2 Then
   WeekendDays = WeekendDays + 1
   ElseIf DatePart("w", NotificationDate) = 4 Then
   WeekendDays = WeekendDays + 1
   ElseIf DatePart("w", NotificationDate) = 6 Then
   WeekendDays = WeekendDays + 1
   End If
   ' This is the increment line you were missing
   NotificationDate = DateAdd("d", 1, NotificationDate)
   Loop Until NotificationDate = OrderDate

   Do
   If DatePart("w", PlacementDate) = 0 Then
   WeekendDays2 = WeekendDays2 + 1
   ElseIf DatePart("w", PlacementDate) = 2 Then
   WeekendDays2 = WeekendDays2 + 1
   ElseIf DatePart("w", PlacementDate) = 4 Then
   WeekendDays2 = WeekendDays2 + 1
   ElseIf DatePart("w", PlacementDate) = 6 Then
   WeekendDays2 = WeekendDays + 1
   End If
   ' This is the increment line you were missing
   PlacementDate = DateAdd("d", 1, PlacementDate)
   Loop Until PlacementDate = ReleaseDate

   ' No need to set Skip equal to the sum, just set ModWeekdays equal to it 
   '   so you can return the value.
   ' Also, remove that last line, which re-calls the function and causes an infinite loop
   ModWeekdays = WeekendDays + WeekendDays2

End Function

Then, you would call it from your button (or whatever you're doing to run this function) like this:

Dim TotalDaysToSkip as Integer

TotalDaysToSkip = ModWeekdays(Date1, Date2, Date3, Date4)

Upvotes: 1

Related Questions