By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,650 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

Using VBA. Trying to subtract dates from one another.

P: 20
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.
Jan 3 '07 #1
Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Expand|Select|Wrap|Line Numbers
  1. DateDiff ('d', Format([Leave Date], '\#dd/mm/yyyy\#'), Format([Return Date], '\#dd/mm/yyyy\#'))
This should give you the three days required.

Mary
Jan 4 '07 #2

NeoPa
Expert Mod 15k+
P: 31,494
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 4 '07 #3

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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Dim nlbOriginator As ComboBox
  4.  
  5. Private Sub LeaveDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  6.     Set nlbOriginator = LeaveDate
  7.     Calendar.Visible = True
  8.     Calendar.SetFocus
  9.     If Not IsNull(nlbOriginator) Then
  10.    Calendar.Value = nlbOriginator.Value
  11. Else
  12.    Calendar.Value = Date
  13. End If
  14.  
  15. End Sub
  16.  
  17. Private Sub ReturnDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  18.     Set nlbOriginator = ReturnDate
  19.     Calendar.Visible = True
  20.     Calendar.SetFocus
  21.     If Not IsNull(nlbOriginator) Then
  22.         Calendar.Value = nlbOriginator.Value
  23.     Else
  24.         Calendar.Value = Date
  25.     End If
  26. End Sub
  27.  
  28. Private Sub Calendar_Click()
  29.     nlbOriginator.Value = Calendar.Value
  30.     nlbOriginator.SetFocus
  31.     Calendar.Visible = False
  32.     Set nlbOriginator = Nothing
  33. 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
Jan 4 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks for the help, though I am still missing something. I have my "LeaveDate" and "ReturnDate" fields, and the calendar coded as so;

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Dim nlbOriginator As ComboBox
  4.  
  5. Private Sub LeaveDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  6.     Set nlbOriginator = LeaveDate
  7.     Calendar.Visible = True
  8.     Calendar.SetFocus
  9.     If Not IsNull(nlbOriginator) Then
  10.    Calendar.Value = nlbOriginator.Value
  11. Else
  12.    Calendar.Value = Date
  13. End If
  14.  
  15. End Sub
  16.  
  17. Private Sub ReturnDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  18.     Set nlbOriginator = ReturnDate
  19.     Calendar.Visible = True
  20.     Calendar.SetFocus
  21.     If Not IsNull(nlbOriginator) Then
  22.         Calendar.Value = nlbOriginator.Value
  23.     Else
  24.         Calendar.Value = Date
  25.     End If
  26. End Sub
  27.  
  28. Private Sub Calendar_Click()
  29.     nlbOriginator.Value = Calendar.Value
  30.     nlbOriginator.SetFocus
  31.     Calendar.Visible = False
  32.     Set nlbOriginator = Nothing
  33. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandXX_Click()
  2.    Me.DaysGone = DateDiff ('d', [Leave Date], [Return Date])
  3. End Sub
  4.  
Mary
Jan 5 '07 #5

P: 20
Expand|Select|Wrap|Line Numbers
  1. 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 ;)
Jan 17 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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 ...

Expand|Select|Wrap|Line Numbers
  1. Me.DaysGone = DateDiff ("d", [Leave Date], [Return Date])
Mary
Jan 17 '07 #7

P: 20
Awesome! Thanks Mary, your an angel.

It works great now, much thanks.
Jan 17 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Awesome! Thanks Mary, your an angel.

It works great now, much thanks.
You're welcome!
Jan 17 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.