Using VBA. Trying to subtract dates from one another.

[Remington]

I am using windows 2000pro with access 2000.

I am trying to make a database for our HR department, that would allow our HR Director to type in an employee's ID number into a form and then select the dates the employee took off from work.

(I have the calander add-in 8.0 setup to easily select the dates already) There are two Date Fields, "Leave Date", and "Return Date" where the calander selections are stored. They are in the 12/25/2006 Format.

There then is a combo box field for the "Type of Absence" the employee took. Options such as "Paid Time Off", "Unpaid Absence", "Paid Vacation",and "Volunteer Hours" are the choices in the combo box.

The HR Director asked if I could have the "Return Date" field value, subtracted from the "Leave Date" field value. Thus giving me the total number of days taken off.

So, Step 1 would be to have the dates subtracted from on another

Step 2. Is to have the total number of days taken off, broke down into hours.

Step3. Depends on wether or not "Paid Vacation" is selected for the "Type of Absence field". Because Paid Vacation is saved up, I plan to make another table for those hours to be manually stored in.

Step 4 will Idealy subtract the hours the employee has taken off from the stored vacation hours. That is all assuming that Paid Vacation is selected.
If "Unpaid Absence" or "Volunteer hours" is selected, they just need totaled up in a table.

Hopein you got all that, my main question, is;

What steps will it take codewise to make my Form subtract the two calendar dates from one another? i.e. "Leave Date" = 12/20/2006 and "Return Date" = 12/23/2006. Subtracting them, should give me 3 days off. Not sure how to start that one.

From there, I have the program down in theory...3 days * 8 hrs a day, so 24 hours which is then subtracted from the X amount of hours stored in the employee's Paid Vacation field.

I am just learning VBA and I am still just a bit uneasy with things. Input is greatly appreciated.

[MMcCarthy]

I am using windows 2000pro with access 2000.

I am trying to make a database for our HR department, that would allow our HR Director to type in an employee's ID number into a form and then select the dates the employee took off from work.

(I have the calander add-in 8.0 setup to easily select the dates already) There are two Date Fields, "Leave Date", and "Return Date" where the calander selections are stored. They are in the 12/25/2006 Format.

There then is a combo box field for the "Type of Absence" the employee took. Options such as "Paid Time Off", "Unpaid Absence", "Paid Vacation",and "Volunteer Hours" are the choices in the combo box.

The HR Director asked if I could have the "Return Date" field value, subtracted from the "Leave Date" field value. Thus giving me the total number of days taken off.

So, Step 1 would be to have the dates subtracted from on another

Step 2. Is to have the total number of days taken off, broke down into hours.

Step3. Depends on wether or not "Paid Vacation" is selected for the "Type of Absence field". Because Paid Vacation is saved up, I plan to make another table for those hours to be manually stored in.

Step 4 will Idealy subtract the hours the employee has taken off from the stored vacation hours. That is all assuming that Paid Vacation is selected.
If "Unpaid Absence" or "Volunteer hours" is selected, they just need totaled up in a table.

Hopein you got all that, my main question, is;

What steps will it take codewise to make my Form subtract the two calendar dates from one another? i.e. "Leave Date" = 12/20/2006 and "Return Date" = 12/23/2006. Subtracting them, should give me 3 days off. Not sure how to start that one.

From there, I have the program down in theory...3 days * 8 hrs a day, so 24 hours which is then subtracted from the X amount of hours stored in the employee's Paid Vacation field.

I am just learning VBA and I am still just a bit uneasy with things. Input is greatly appreciated.

DateDiff ('d', Format([Leave Date], '\#dd/mm/yyyy\#'), Format([Return Date], '\#dd/mm/yyyy\#'))

This should give you the three days required.

Mary

[NeoPa]

DateDiff ('d', Format([Leave Date], '\#dd/mm/yyyy\#'), Format([Return Date], '\#dd/mm/yyyy\#'))

This should give you the three days required.

Mary

Nice to see the delimiters in there nicely Mary. If I'm being picky (Moi? - Jamais!) the date format should be m/d rather than d/m in SQL. Otherwise it's perfect.
In fact, in this case, literals are probably not required though. That means you should get away with simply :

DateDiff ('d', [Leave Date], [Return Date])

This code can be used even within your SQL.

One thing I would point out is that this makes no allowance for weekends or holidays. That is a whole new can of worms and frequently requires special VBA code to handle.

[Remington]

Thanks for the help, though I am still missing something. I have my "LeaveDate" and "ReturnDate" fields, and the calendar coded as so;

Option Compare Database
Option Explicit
Dim nlbOriginator As ComboBox
 
Private Sub LeaveDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Set nlbOriginator = LeaveDate
    Calendar.Visible = True
    Calendar.SetFocus
    If Not IsNull(nlbOriginator) Then
   Calendar.Value = nlbOriginator.Value
Else
   Calendar.Value = Date
End If
 
End Sub
 
Private Sub ReturnDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Set nlbOriginator = ReturnDate
    Calendar.Visible = True
    Calendar.SetFocus
    If Not IsNull(nlbOriginator) Then
        Calendar.Value = nlbOriginator.Value
    Else
        Calendar.Value = Date
    End If
End Sub
 
Private Sub Calendar_Click()
    nlbOriginator.Value = Calendar.Value
    nlbOriginator.SetFocus
    Calendar.Visible = False
    Set nlbOriginator = Nothing
End Sub

The text box, I have set to recieve the calculated number of days gone, is called "DaysGone". I assume I need to set another global value, to hold the value of the two dates being subtracted? Then I make another sub procedure, for the calendar or for the "DaysGone" field? (It would be there that I insert your listed code?)

I could be way off, any thoughts?

Thanks,
Remington

[MMcCarthy]

Thanks for the help, though I am still missing something. I have my "LeaveDate" and "ReturnDate" fields, and the calendar coded as so;

Option Compare Database
Option Explicit
Dim nlbOriginator As ComboBox
 
Private Sub LeaveDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Set nlbOriginator = LeaveDate
    Calendar.Visible = True
    Calendar.SetFocus
    If Not IsNull(nlbOriginator) Then
   Calendar.Value = nlbOriginator.Value
Else
   Calendar.Value = Date
End If
 
End Sub
 
Private Sub ReturnDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Set nlbOriginator = ReturnDate
    Calendar.Visible = True
    Calendar.SetFocus
    If Not IsNull(nlbOriginator) Then
        Calendar.Value = nlbOriginator.Value
    Else
        Calendar.Value = Date
    End If
End Sub
 
Private Sub Calendar_Click()
    nlbOriginator.Value = Calendar.Value
    nlbOriginator.SetFocus
    Calendar.Visible = False
    Set nlbOriginator = Nothing
End Sub

The text box, I have set to recieve the calculated number of days gone, is called "DaysGone". I assume I need to set another global value, to hold the value of the two dates being subtracted? Then I make another sub procedure, for the calendar or for the "DaysGone" field? (It would be there that I insert your listed code?)

I could be way off, any thoughts?

Thanks,
Remington

If you have the field "DaysGone" on your form. Then you just have to decide when you want to trigger this event. You could put the code behind a command button and just code as follows.

Private Sub CommandXX_Click()
   Me.DaysGone = DateDiff ('d', [Leave Date], [Return Date])
End Sub
 

Mary

[Remington]

Me.DaysGone = DateDiff ('d', [Leave Date], [Return Date])

I am not having any luck getting this to work. I keep getting an error, wich takes me to the (' just before the d',[Leave Date]

Other questions: What is the Me. ??

Sorry to be a pain ;)

[MMcCarthy]

Me.DaysGone = DateDiff ('d', [Leave Date], [Return Date])


I am not having any luck getting this to work. I keep getting an error, wich takes me to the (' just before the d',[Leave Date]

Other questions: What is the Me. ??

Sorry to be a pain ;)

Not a problem.

Me. is just a way of referring to the current form.

Sorry this was my error. Try this instead ...

Me.DaysGone = DateDiff ("d", [Leave Date], [Return Date])

Mary

[Remington]

Awesome! Thanks Mary, your an angel.

It works great now, much thanks.

[MMcCarthy]

Awesome! Thanks Mary, your an angel.

It works great now, much thanks.

You're welcome!