Home Ask Login Register

Developers Planet

Your answer is one click away!

D.Smith February 2016

MS Access VBA - system crashing during do until loop

I'm trying to calculate the number of days between two dates on a form, excluding weekends. However, the system keeps crashing every time it runs the do until loop. Can you spot where I might be going wrong within the code below?

Thanks for your help!

Private Sub total_text_Click()
Dim v_weekendcount As Integer
Dim v_date As Date
Dim dateone As Date
Dim datetwo As Date

v_date = Me.startdate_text.Value
v_weekendcount = 0

Do Until v_date = Me.enddate_text.Value
If Day(v_date) = vbSaturday Or Day(v_date) = vbSunday Then
v_weekendcount = v_weekendcount + 1
v_date = v_date + 1
End If

dateone = Me.startdate_text.Value
datetwo = Me.enddate_text.Value

Me.total_text.Value = DateDiff("d", dateone, datetwo) - v_weekendcount

End Sub


Tom February 2016

Just to exclude the possibility change the following line accordingly:

If (Day(v_date) = vbSaturday) Or (Day(v_date) = vbSunday) Then

JJ32 February 2016

You have the v_date = v_date + 1 call within the check for weekend. Doesn't this mean when it's not a weekend day it won't increment the date?

Gustav February 2016

You probably had this in mind:

Do Until v_date = DateValue(Me.enddate_text.Value)
    If Weekday(v_date) = vbSaturday Or Weekday(v_date) = vbSunday Then
        v_weekendcount = v_weekendcount + 1
    End If
    v_date = v_date + 1

Post Status

Asked in February 2016
Viewed 1,213 times
Voted 7
Answered 3 times


Leave an answer

Quote of the day: live life