Date comparison

[michane]

[SOLVED] Date comparison

What is wrong with the code below, it keep having "Run-time error '2185': You can't reference a property or method for a control unless the control has the focus."

I just want to calculate the difference of the days with the input txtStartDate.Text & txtEndDate.Text on the form & fill another textbox, txtDaysLeave.Text, with the days difference.

Private Sub txtEndDate_Change()
    Dim intDays As Integer
    intDays = DateDiff("d", txtStartDate.Text, txtEndDate.Text)
    txtDaysLeave.Text = CStr(intDays)
End Sub

The error happens on the line when I compare the start date(txtStartDate.Text) & end date(txtEndDate.Text).
-> Both are textboxes (however they come with date picker tool when clicked)

These textbox corresponds to date field of my table, Leave_Details.

 

[John Big Booty]

Try;

Private Sub txtEndDate_Change()
    Dim intDays As Integer
    intDays = DateDiff("d", Me.txtStartDate, Me.txtEndDate)
    Me.txtDaysLeave = CStr(intDays)
End Sub

 

[John Big Booty]

Or even;

Private Sub txtEndDate_Change()
    Me.txtDaysLeave = DateDiff("d", Me.txtStartDate, Me.txtEndDate)
End Sub

 

[michane]

The following is to enhance the method above..
I just found from office.com help content, "How to: Find the Number of Working Days Between Two Dates" (useful especially in applications for staff leave, which is what I'm working on.. however, this function does not account for holidays) :

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer 
 
 Dim WholeWeeks As Variant 
 Dim DateCnt As Variant 
 Dim EndDays As Integer 
 
 On Error GoTo Err_Work_Days 
 
 BegDate = DateValue(BegDate) 
 EndDate = DateValue(EndDate) 
 WholeWeeks = DateDiff("w", BegDate, EndDate) 
 DateCnt = DateAdd("ww", WholeWeeks, BegDate) 
 EndDays = 0 
 
 Do While DateCnt <= EndDate 
 If Format(DateCnt, "ddd") <> "Sun" And _ 
 Format(DateCnt, "ddd") <> "Sat" Then 
 EndDays = EndDays + 1 
 End If 
 DateCnt = DateAdd("d", 1, DateCnt) 
 Loop 
 
 Work_Days = WholeWeeks * 5 + EndDays 
 
Exit Function 
 
 Err_Work_Days: 
 
 ' If either BegDate or EndDate is Null, return a zero 
 ' to indicate that no workdays passed between the two dates. 
 
 If Err.Number = 94 Then 
 Work_Days = 0 
 Exit Function 
 Else 
' If some other error occurs, provide a message. 
 MsgBox "Error " & Err.Number & ": " & Err.Description 
 End If 
 
End Function

Then after inserting the function above, I just need to call the function (Work_Days), inserting input from my form (in my case, it's textboxes - txtStartDate & txtEndDate) :

'If the textbox txtEndDate changes then calculate the days of leave.
Private Sub txtEndDate_Change()
    Dim intDays As Integer
    intDays = Work_Days(Me.txtStartDate, Me.txtEndDate)
    Me.txtDaysLeave = intDays
End Sub

Once again, really appreciate John for helping out earlier

 

[Brianwarnock]

There is code in this thread

http://www.access-programmers.co.uk/forums/showthread.php?t=240997

Brian

 

[Brianwarnock]

Also code and explanations in this thread

http://www.utteraccess.com/forum/lofiversion/index.php/t1964157.html

Brian

 

[michane]

Thank you brian for sharing!
Is the holiday function able to detect holidays other than US
Can it determine local holidays just by setting the computer "Regional" settings to local setting?

 

[Brianwarnock]

There is nothing automatic about the holidays, you create a table of the holidays that do not occur at weekends, weekends are taken care of automatically, although I can see in the modern age that might not be always appropriate , you need to study the code and explanations to see if it is right for you.

Brian

 

[michane]

IMHO I rather not put it in holidays then, since it is abit of a trouble. I cannot say for sure that there will be someone maintaining the table at all time.. I will not have it done then next person who takes over has to take the trouble maintaining it *duh*
Anyway, for my case.. the staff applying leave not supposed to use a leave application for any holidays. Don't you agree?!
Also there will be a "Remarks" text field in the application in any case that staff is to take leave over a holiday eg. Christmas eve, on unforeseen circumstances.. which I have previously witnessed

 

[Brianwarnock]

I don't know your setup but I would envisage this application being run by staff dept, or Human Resources as it is now known, and thus they would maintain the Bank Holiday Table via a form, it would be a simple update near the end of each year when the following years dates are published.

Brian

 

[michane]

Hi Peter, thank you for the explanations, you're right.. need to place the codes in Form_BeforeUpdate event. However is there a better option?
I still hope it can be displayed on the txtDaysLeave, right after the user finishes selecting the dates. So that the page does not need to turn away to have it updated.

 

[michane]

Thank you for all the comments
I have modified my code to:

Private Sub txtStartDate_AfterUpdate()
    If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
        If DateDiff("d", Me.txtStartDate, Me.txtEndDate) >= 0 Then
            Dim intDays As Integer
            intDays = Work_Days(Me.txtStartDate, Me.txtEndDate)
            Me.txtDaysLeave = intDays
        Else
            MsgBox "Please check - Start date must not come after end date!"
            Me.txtStartDate = ""
            Me.txtDaysLeave = ""
        End If
    End If
End Sub
 
Private Sub txtEndDate_AfterUpdate()
    If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
        If DateDiff("d", Me.txtStartDate, Me.txtEndDate) >= 0 Then
            Dim intDays As Integer
            intDays = Work_Days(Me.txtStartDate, Me.txtEndDate)
            Me.txtDaysLeave = intDays
        Else
            MsgBox "Please check - End date must not come before start date!"
            Me.txtEndDate = ""
            Me.txtDaysLeave = ""
        End If
    End If
End Sub

Basically as suggested by Pat, both AfterUpdate codes the same.
DateDiff helps me to check that the end date minus the start date is not negative, thus users will get a msgbox if they enter a start date after the end date / end date that is before the start