Calculating Vacation Time

denileigh · Feb 6, 2004

I am trying to figure out an expression to calculate vacation time for our employees.

Employees get 40 hours after 1 year
They get 80 hours as of Jan. 1 after that year
They get 120 hours after 10 years.

So, if an employee is hired on Dec, 15, 2003 then as of Dec. 15, 2004 he will have 40 hours but then 2 weeks later as of Jan 1, he will get 80.

I have NO idea how to do this. I have some calculations in text boxes on reports but I'l like to add this one to a query and don't think that the IIF code will work there.

I came up with something like this but I KNOW it isn't right, especially when it comes to the January thing.

IIF Month(DateToday)-[HireDate]>365, 0, IIF Month(DateToday)-[HireDate]>365, 40, IIF Month(DateToday)-[HireDate]>365 AND >Month(January 1), 80, IIF Month(DateToday)-[HireDate]>3650, 120

Do you use IIF in a query? Is there a good book with references to this sort of thing in it?

Can anyone help? Thanks so much!

dan-cat · Feb 7, 2004

I've had fun with this one this morning.

Check out the code behind the On load event of the form - hope this helps

denileigh · Feb 12, 2004

Thank you soooo much! Sorry for the delayed response...I will play with that today!

*smiles*

denileigh · Feb 12, 2004

I am getting the error below...am I doing something wrong?

Mile-O · Feb 12, 2004

denileigh said:
I am getting the error below...am I doing something wrong?

Nope. Follow me...

From looking at the image I'd say that some of the code is rather laborious, too. No offence, dan.

denileigh · Feb 12, 2004

Thanks! I think I fixed that. I am using 2003 but now when I go to open the form I get a "run-time error "13". Type mismatch....

When I click debug it goes to:

Set db = CurrentDb()

????

This one is gonna make me pull my hair out isn't it?

How come the code worked fine in his sample db and not when I put it in mine? Maybe his was created in an earlier version huh?

Mile-O · Feb 12, 2004

Did you set the reference properly?
Do you have Dim db As DAO.Database as a line?

Post the code you have, if not.

denileigh · Feb 12, 2004

Hmmm...I have...

Private Sub Form_Load()
Dim db As DAO.Database ' DAO
Dim rs As DAO.Recordset ' DAO
Dim datHireDate As Integer
Dim dat40 As Integer
Dim dat80 As Integer
Dim dat120 As Integer
Dim strStartDate As String

Set db = CurrentDb()
Set rec = db.OpenRecordset("EMPLOYEES")
Do Until rec.EOF
strStartDate = rec.Fields("HireDate")
datEmployed = DateDiff("d", strHireDate, Date)
dat40 = DateDiff("d", strHireDate, DateAdd("yyyy", 1, strHireDate))
dat80 = DateDiff("d", strHireDate, "01/01/" & Year(DateAdd("yyyy", 2, strHireDate)))
dat120 = DateDiff("d", strHireDate, DateAdd("yyyy", 10, strHireDate))

If datHireDate < dat40 Then
rec.Edit
rec.Fields("VacHrsAvailable") = "0"
rec.Update
End If
If datHireDate >= dat40 And datHireDate < dat80 Then
rec.Edit
rec.Fields("VacHrsAvailable") = "40"
rec.Update
End If
If datHireDate >= dat80 And datHireDate < dat120 Then
rec.Edit
rec.Fields("VacHrsAvailable") = "80"
rec.Update
End If
rec.Edit
If datHireDate >= dat120 Then
rec.Fields("VacHrsAvailable") = "120"
rec.Update
End If
rec.MoveNext
Loop
rec.Close
End Sub

I am in Access 2003 on WIndows 2000. Checked references are (in order)

Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft Word 10.0 Object Library
Microsoft Office 11.0 Object Library
Miscrosoft DAO 3.0 Object Library
Microsoft ActiveX Data Objects 2.1 Library

Dunno if you needed that but it was in the link you sent so I thought maybe it might matter.

Mile-O · Feb 12, 2004

Change: Dim rs As DAO.Recordset ' DAO
To: Dim rec As DAO.Recordset ' DAO

denileigh · Feb 12, 2004

Ugh! done...same error...type 13 mismatch...when I click debug it keeps going to

Set db = CurrentDb()

and highlighting it

Mile-O · Feb 12, 2004

denileigh said:
Set db = CurrentDb()

Well, I'm lost now. Does CurrentDb really have brackets after it?

denileigh · Feb 12, 2004

Not any more lost than I am being a virtual Access virgin and all....lol

Lemme check...

Yup and I took them out....same error.

Double Ugh

dan-cat · Feb 12, 2004

Mile-O-Phile said:
Nope. Follow me...

From looking at the image I'd say that some of the code is rather laborious, too. No offence, dan.

Laborious? Well maybe it's a little too ornate

I admit I got a little carried away with this one

Make sure that your DAO reference is set higher up the list than your ActiveX Data Object reference. Set the proirity by using the up and down errors displayed in the reference dialog box.

denileigh · Feb 12, 2004

I just got the error to go away...I mean JUST now...I had DAO3.0 checked and I changed it to 3.6.

No more error as far as that but I'm getting an 0 in the vacation field and it's putting that into the employee table too on everyone. Dates are calculating just fine though as far as the 40, 80 and 120.

Mile-O · Feb 12, 2004

dan-cat, it already is.

denileigh said:
I am in Access 2003 on WIndows 2000. Checked references are (in order)

Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft Word 10.0 Object Library
Microsoft Office 11.0 Object Library
Miscrosoft DAO 3.0 Object Library
Microsoft ActiveX Data Objects 2.1 Library

Try unchecking the ActiveX Data Objects, remove that DAO reference too, and select a higher one: 3.5 or 3.6.

Move it above the Word Object Library.

dan-cat · Feb 12, 2004

denileigh said:
I just got the error to go away...I mean JUST now...I had DAO3.0 checked and I changed it to 3.6.

No more error as far as that but I'm getting an 0 in the vacation field and it's putting that into the employee table too on everyone. Dates are calculating just fine though as far as the 40, 80 and 120.

Whats the earliest hiredate you have in your employees table?

denileigh · Feb 13, 2004

dan-cat · Feb 13, 2004

denileigh said:
1983

Right, I know my code is a work of art lol

Is it possible to post or email a copy of your db so I can see what the problem is?

denileigh · Feb 13, 2004

I wish but I think it's like 15mg or so...is there any way to delete all of the info in all the tables really quick?

Mile-O · Feb 13, 2004

Make a blank database, import what you need, and delete the personal information.

Compact the database, .zip it, and upload it.

Calculating Vacation Time

Registered User.

Registered User.

Attachments

Registered User.

Registered User.

Back once again...

Registered User.

Back once again...

Registered User.

Back once again...

Registered User.

Back once again...

Registered User.

Registered User.

Registered User.

Back once again...

Registered User.

Registered User.

Registered User.

Registered User.

Back once again...

Similar threads

Users who are viewing this thread