Vacation Query

What I need to figure out now is how to get a query to look at their
anniversary date and add in what amount they get awarded and also to deduct
what they have taken to date. When a new employee is eligible for vacation is
on their anniversary date and then they can start taking it. So, if someone
starts in march they have to wait until the following march to use their
vacation. Can anyone help?

Need more information.
How do you know how much vacation an employee is eligible for?
How do you know how much they have used in the current year?

"Julie" wrote:

What I need to figure out now is how to get a query to look at their
anniversary date and add in what amount they get awarded and also to deduct
what they have taken to date. When a new employee is eligible for vacation is
on their anniversary date and then they can start taking it. So, if someone
starts in march they have to wait until the following march to use their
vacation. Can anyone help?

I have set up a table to look up for each person. Here is an example
1 year - 40 hrs
2 years - 80 hrs
7 years - 120 hrs
12 years - 160 hrs.

I have designed a form that inputs everyone's vacation for the year. I just
don't know how to get access to look at what time they have taken and deduct
it from what they were awarded for that year.

"Klatuu" wrote:

Need more information.
How do you know how much vacation an employee is eligible for?
How do you know how much they have used in the current year?

"Julie" wrote:

What I need to figure out now is how to get a query to look at their
anniversary date and add in what amount they get awarded and also to deduct
what they have taken to date. When a new employee is eligible for vacation is
on their anniversary date and then they can start taking it. So, if someone
starts in march they have to wait until the following march to use their
vacation. Can anyone help?

Do you have a field that shows how much time has been taken for the current
year?
There is probably a little complexity here in that your vacation allotments
are probably calculated on an employee's original hire date, but what time is
actually used is calculated on a calendar date (Jan 1 - Dec 31).

In any case, here is a function originally written to determine a person's
age as of a date. It will work for you in determining how long a person has
been employed.

Function CalcAge(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today if
missing.
'Return: Whole number of years.
Dim dtmDOB As Date
Dim dtmAsOf As Date
Dim dtmBDay As Date 'Birthday in the year of calculation.

CalcAge = Null 'Initialize to Null

'Validate parameters
If IsDate(varDOB) Then
dtmDOB = varDOB

If IsDate(varAsOf) Then 'Date to calculate age from.
dtmAsOf = varAsOf
Else
dtmAsOf = date
End If

If dtmAsOf = dtmDOB Then 'Calculate only if it's after person was
born.
dtmBDay = DateSerial(year(dtmAsOf), Month(dtmDOB), Day(dtmDOB))
CalcAge = DateDiff("yyyy", dtmDOB, dtmAsOf) + (dtmBDay dtmAsOf)
Else: CalcAge = Null 'Return Null if AsOf is before birthday
End If
End If
End Function

You can determine how many hours they are eligible for with this:

Function VacHoursEarned(intYears AS integer) As Integer

Select Case intYears
Case is 1
intYears = 40
Case is 2 To 6
intYears = 80
Case is 7 To 11
intYears = 120
Case is =12
intYears = 160
End Select
End Function




"Julie" wrote:

I have set up a table to look up for each person. Here is an example
1 year - 40 hrs
2 years - 80 hrs
7 years - 120 hrs
12 years - 160 hrs.

I have designed a form that inputs everyone's vacation for the year. I just
don't know how to get access to look at what time they have taken and deduct
it from what they were awarded for that year.

"Klatuu" wrote:

Need more information.
How do you know how much vacation an employee is eligible for?
How do you know how much they have used in the current year?

"Julie" wrote:

What I need to figure out now is how to get a query to look at their
anniversary date and add in what amount they get awarded and also to deduct
what they have taken to date. When a new employee is eligible for vacation is
on their anniversary date and then they can start taking it. So, if someone
starts in march they have to wait until the following march to use their
vacation. Can anyone help?

Hi,
I want to use your function to determine the Vacation hours earned but I'm
unsure how to have it look at the textbox on my form to determine which case
it should use. Can you help shed some light on how I would set this up? I
have a form with a textbox on it that calculates a persons time of employment
from their hiredate until the present date. Now I want the data in that field
to be compared to the select case function you wrote below and have it
populate the hours earned into another textbox.
Thanks
SS

"Klatuu" wrote:

Do you have a field that shows how much time has been taken for the current
year?
There is probably a little complexity here in that your vacation allotments
are probably calculated on an employee's original hire date, but what time is
actually used is calculated on a calendar date (Jan 1 - Dec 31).

In any case, here is a function originally written to determine a person's
age as of a date. It will work for you in determining how long a person has
been employed.

Function CalcAge(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today if
missing.
'Return: Whole number of years.
Dim dtmDOB As Date
Dim dtmAsOf As Date
Dim dtmBDay As Date 'Birthday in the year of calculation.

CalcAge = Null 'Initialize to Null

'Validate parameters
If IsDate(varDOB) Then
dtmDOB = varDOB

If IsDate(varAsOf) Then 'Date to calculate age from.
dtmAsOf = varAsOf
Else
dtmAsOf = date
End If

If dtmAsOf = dtmDOB Then 'Calculate only if it's after person was
born.
dtmBDay = DateSerial(year(dtmAsOf), Month(dtmDOB), Day(dtmDOB))
CalcAge = DateDiff("yyyy", dtmDOB, dtmAsOf) + (dtmBDay dtmAsOf)
Else: CalcAge = Null 'Return Null if AsOf is before birthday
End If
End If
End Function

You can determine how many hours they are eligible for with this:

Function VacHoursEarned(intYears AS integer) As Integer

Select Case intYears
Case is 1
intYears = 40
Case is 2 To 6
intYears = 80
Case is 7 To 11
intYears = 120
Case is =12
intYears = 160
End Select
End Function




"Julie" wrote:

I have set up a table to look up for each person. Here is an example
1 year - 40 hrs
2 years - 80 hrs
7 years - 120 hrs
12 years - 160 hrs.

I have designed a form that inputs everyone's vacation for the year. I just
don't know how to get access to look at what time they have taken and deduct
it from what they were awarded for that year.

"Klatuu" wrote:

Need more information.
How do you know how much vacation an employee is eligible for?
How do you know how much they have used in the current year?

"Julie" wrote:

What I need to figure out now is how to get a query to look at their
anniversary date and add in what amount they get awarded and also to deduct
what they have taken to date. When a new employee is eligible for vacation is
on their anniversary date and then they can start taking it. So, if someone
starts in march they have to wait until the following march to use their
vacation. Can anyone help?

If you want to use the value in a text box, just pass the value in the text
box as the function's argument:
VacHoursEarned(Me.txtYrsService)

"Secret Squirrel" wrote:

Hi,
I want to use your function to determine the Vacation hours earned but I'm
unsure how to have it look at the textbox on my form to determine which case
it should use. Can you help shed some light on how I would set this up? I
have a form with a textbox on it that calculates a persons time of employment
from their hiredate until the present date. Now I want the data in that field
to be compared to the select case function you wrote below and have it
populate the hours earned into another textbox.
Thanks
SS

"Klatuu" wrote:

Do you have a field that shows how much time has been taken for the current
year?
There is probably a little complexity here in that your vacation allotments
are probably calculated on an employee's original hire date, but what time is
actually used is calculated on a calendar date (Jan 1 - Dec 31).

In any case, here is a function originally written to determine a person's
age as of a date. It will work for you in determining how long a person has
been employed.

Function CalcAge(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today if
missing.
'Return: Whole number of years.
Dim dtmDOB As Date
Dim dtmAsOf As Date
Dim dtmBDay As Date 'Birthday in the year of calculation.

CalcAge = Null 'Initialize to Null

'Validate parameters
If IsDate(varDOB) Then
dtmDOB = varDOB

If IsDate(varAsOf) Then 'Date to calculate age from.
dtmAsOf = varAsOf
Else
dtmAsOf = date
End If

If dtmAsOf = dtmDOB Then 'Calculate only if it's after person was
born.
dtmBDay = DateSerial(year(dtmAsOf), Month(dtmDOB), Day(dtmDOB))
CalcAge = DateDiff("yyyy", dtmDOB, dtmAsOf) + (dtmBDay dtmAsOf)
Else: CalcAge = Null 'Return Null if AsOf is before birthday
End If
End If
End Function

You can determine how many hours they are eligible for with this:

Function VacHoursEarned(intYears AS integer) As Integer

Select Case intYears
Case is 1
intYears = 40
Case is 2 To 6
intYears = 80
Case is 7 To 11
intYears = 120
Case is =12
intYears = 160
End Select
End Function




"Julie" wrote:

I have set up a table to look up for each person. Here is an example
1 year - 40 hrs
2 years - 80 hrs
7 years - 120 hrs
12 years - 160 hrs.

I have designed a form that inputs everyone's vacation for the year. I just
don't know how to get access to look at what time they have taken and deduct
it from what they were awarded for that year.

"Klatuu" wrote:

Need more information.
How do you know how much vacation an employee is eligible for?
How do you know how much they have used in the current year?

"Julie" wrote:

What I need to figure out now is how to get a query to look at their
anniversary date and add in what amount they get awarded and also to deduct
what they have taken to date. When a new employee is eligible for vacation is
on their anniversary date and then they can start taking it. So, if someone
starts in march they have to wait until the following march to use their
vacation. Can anyone help?

How do I call this function from the textbox where I want it to populate the
hours earned?

"Klatuu" wrote:

If you want to use the value in a text box, just pass the value in the text
box as the function's argument:
VacHoursEarned(Me.txtYrsService)

"Secret Squirrel" wrote:

Hi,
I want to use your function to determine the Vacation hours earned but I'm
unsure how to have it look at the textbox on my form to determine which case
it should use. Can you help shed some light on how I would set this up? I
have a form with a textbox on it that calculates a persons time of employment
from their hiredate until the present date. Now I want the data in that field
to be compared to the select case function you wrote below and have it
populate the hours earned into another textbox.
Thanks
SS

"Klatuu" wrote:

Do you have a field that shows how much time has been taken for the current
year?
There is probably a little complexity here in that your vacation allotments
are probably calculated on an employee's original hire date, but what time is
actually used is calculated on a calendar date (Jan 1 - Dec 31).

In any case, here is a function originally written to determine a person's
age as of a date. It will work for you in determining how long a person has
been employed.

Function CalcAge(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today if
missing.
'Return: Whole number of years.
Dim dtmDOB As Date
Dim dtmAsOf As Date
Dim dtmBDay As Date 'Birthday in the year of calculation.

CalcAge = Null 'Initialize to Null

'Validate parameters
If IsDate(varDOB) Then
dtmDOB = varDOB

If IsDate(varAsOf) Then 'Date to calculate age from.
dtmAsOf = varAsOf
Else
dtmAsOf = date
End If

If dtmAsOf = dtmDOB Then 'Calculate only if it's after person was
born.
dtmBDay = DateSerial(year(dtmAsOf), Month(dtmDOB), Day(dtmDOB))
CalcAge = DateDiff("yyyy", dtmDOB, dtmAsOf) + (dtmBDay dtmAsOf)
Else: CalcAge = Null 'Return Null if AsOf is before birthday
End If
End If
End Function

You can determine how many hours they are eligible for with this:

Function VacHoursEarned(intYears AS integer) As Integer

Select Case intYears
Case is 1
intYears = 40
Case is 2 To 6
intYears = 80
Case is 7 To 11
intYears = 120
Case is =12
intYears = 160
End Select
End Function




"Julie" wrote:

I have set up a table to look up for each person. Here is an example
1 year - 40 hrs
2 years - 80 hrs
7 years - 120 hrs
12 years - 160 hrs.

I have designed a form that inputs everyone's vacation for the year. I just
don't know how to get access to look at what time they have taken and deduct
it from what they were awarded for that year.

"Klatuu" wrote:

Need more information.
How do you know how much vacation an employee is eligible for?
How do you know how much they have used in the current year?

"Julie" wrote:

What I need to figure out now is how to get a query to look at their
anniversary date and add in what amount they get awarded and also to deduct
what they have taken to date. When a new employee is eligible for vacation is
on their anniversary date and then they can start taking it. So, if someone
starts in march they have to wait until the following march to use their
vacation. Can anyone help?

Assuming it is not a bound field (and it should not be, because it violates
the rule on storing calculated values), you can put it in the Control Source
property for the text box. To ensure the values needed are available, see
the modification below:
(Also I noticed the function had a missing line of code, so it would never
return anything

Function VacHoursEarned(varYears AS Variant) As Integer

If IsNull(varYears) Then
VacHoursEarned = Null
Exit Function
End If
Select Case varYears
Case is 1
VacHoursEarned = 40
Case is 2 To 6
VacHoursEarned = 80
Case is 7 To 11
VacHoursEarned = 120
Case is =12
VacHoursEarned = 160
End Select
End Function


"Secret Squirrel" wrote:

How do I call this function from the textbox where I want it to populate the
hours earned?

"Klatuu" wrote:

If you want to use the value in a text box, just pass the value in the text
box as the function's argument:
VacHoursEarned(Me.txtYrsService)

"Secret Squirrel" wrote:

Hi,
I want to use your function to determine the Vacation hours earned but I'm
unsure how to have it look at the textbox on my form to determine which case
it should use. Can you help shed some light on how I would set this up? I
have a form with a textbox on it that calculates a persons time of employment
from their hiredate until the present date. Now I want the data in that field
to be compared to the select case function you wrote below and have it
populate the hours earned into another textbox.
Thanks
SS

"Klatuu" wrote:

Do you have a field that shows how much time has been taken for the current
year?
There is probably a little complexity here in that your vacation allotments
are probably calculated on an employee's original hire date, but what time is
actually used is calculated on a calendar date (Jan 1 - Dec 31).

In any case, here is a function originally written to determine a person's
age as of a date. It will work for you in determining how long a person has
been employed.

Function CalcAge(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today if
missing.
'Return: Whole number of years.
Dim dtmDOB As Date
Dim dtmAsOf As Date
Dim dtmBDay As Date 'Birthday in the year of calculation.

CalcAge = Null 'Initialize to Null

'Validate parameters
If IsDate(varDOB) Then
dtmDOB = varDOB

If IsDate(varAsOf) Then 'Date to calculate age from.
dtmAsOf = varAsOf
Else
dtmAsOf = date
End If

If dtmAsOf = dtmDOB Then 'Calculate only if it's after person was
born.
dtmBDay = DateSerial(year(dtmAsOf), Month(dtmDOB), Day(dtmDOB))
CalcAge = DateDiff("yyyy", dtmDOB, dtmAsOf) + (dtmBDay dtmAsOf)
Else: CalcAge = Null 'Return Null if AsOf is before birthday
End If
End If
End Function

You can determine how many hours they are eligible for with this:

Function VacHoursEarned(intYears AS integer) As Integer

Select Case intYears
Case is 1
intYears = 40
Case is 2 To 6
intYears = 80
Case is 7 To 11
intYears = 120
Case is =12
intYears = 160
End Select
End Function




"Julie" wrote:

I have set up a table to look up for each person. Here is an example
1 year - 40 hrs
2 years - 80 hrs
7 years - 120 hrs
12 years - 160 hrs.

I have designed a form that inputs everyone's vacation for the year. I just
don't know how to get access to look at what time they have taken and deduct
it from what they were awarded for that year.

"Klatuu" wrote:

Need more information.
How do you know how much vacation an employee is eligible for?
How do you know how much they have used in the current year?

"Julie" wrote:

What I need to figure out now is how to get a query to look at their
anniversary date and add in what amount they get awarded and also to deduct
what they have taken to date. When a new employee is eligible for vacation is
on their anniversary date and then they can start taking it. So, if someone
starts in march they have to wait until the following march to use their
vacation. Can anyone help?

Thanks for that Klatuu but I have one more question. How do I tell this code
to look at my textbox "Years" to find out which case it falls into?

"Klatuu" wrote:

Assuming it is not a bound field (and it should not be, because it violates
the rule on storing calculated values), you can put it in the Control Source
property for the text box. To ensure the values needed are available, see
the modification below:
(Also I noticed the function had a missing line of code, so it would never
return anything

Function VacHoursEarned(varYears AS Variant) As Integer

If IsNull(varYears) Then
VacHoursEarned = Null
Exit Function
End If
Select Case varYears
Case is 1
VacHoursEarned = 40
Case is 2 To 6
VacHoursEarned = 80
Case is 7 To 11
VacHoursEarned = 120
Case is =12
VacHoursEarned = 160
End Select
End Function


"Secret Squirrel" wrote:

How do I call this function from the textbox where I want it to populate the
hours earned?

"Klatuu" wrote:

If you want to use the value in a text box, just pass the value in the text
box as the function's argument:
VacHoursEarned(Me.txtYrsService)

"Secret Squirrel" wrote:

Hi,
I want to use your function to determine the Vacation hours earned but I'm
unsure how to have it look at the textbox on my form to determine which case
it should use. Can you help shed some light on how I would set this up? I
have a form with a textbox on it that calculates a persons time of employment
from their hiredate until the present date. Now I want the data in that field
to be compared to the select case function you wrote below and have it
populate the hours earned into another textbox.
Thanks
SS

"Klatuu" wrote:

Do you have a field that shows how much time has been taken for the current
year?
There is probably a little complexity here in that your vacation allotments
are probably calculated on an employee's original hire date, but what time is
actually used is calculated on a calendar date (Jan 1 - Dec 31).

In any case, here is a function originally written to determine a person's
age as of a date. It will work for you in determining how long a person has
been employed.

Function CalcAge(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today if
missing.
'Return: Whole number of years.
Dim dtmDOB As Date
Dim dtmAsOf As Date
Dim dtmBDay As Date 'Birthday in the year of calculation.

CalcAge = Null 'Initialize to Null

'Validate parameters
If IsDate(varDOB) Then
dtmDOB = varDOB

If IsDate(varAsOf) Then 'Date to calculate age from.
dtmAsOf = varAsOf
Else
dtmAsOf = date
End If

If dtmAsOf = dtmDOB Then 'Calculate only if it's after person was
born.
dtmBDay = DateSerial(year(dtmAsOf), Month(dtmDOB), Day(dtmDOB))
CalcAge = DateDiff("yyyy", dtmDOB, dtmAsOf) + (dtmBDay dtmAsOf)
Else: CalcAge = Null 'Return Null if AsOf is before birthday
End If
End If
End Function

You can determine how many hours they are eligible for with this:

Function VacHoursEarned(intYears AS integer) As Integer

Select Case intYears
Case is 1
intYears = 40
Case is 2 To 6
intYears = 80
Case is 7 To 11
intYears = 120
Case is =12
intYears = 160
End Select
End Function




"Julie" wrote:

I have set up a table to look up for each person. Here is an example
1 year - 40 hrs
2 years - 80 hrs
7 years - 120 hrs
12 years - 160 hrs.

I have designed a form that inputs everyone's vacation for the year. I just
don't know how to get access to look at what time they have taken and deduct
it from what they were awarded for that year.

"Klatuu" wrote:

Need more information.
How do you know how much vacation an employee is eligible for?
How do you know how much they have used in the current year?

"Julie" wrote:

What I need to figure out now is how to get a query to look at their
anniversary date and add in what amount they get awarded and also to deduct
what they have taken to date. When a new employee is eligible for vacation is
on their anniversary date and then they can start taking it. So, if someone
starts in march they have to wait until the following march to use their
vacation. Can anyone help?

To show the vacation earned in your text box. Make this the control souce
for the text box. It will pass the value from the Years text box to it:

=VacHoursEarned([Years])

"Secret Squirrel" wrote:

Thanks for that Klatuu but I have one more question. How do I tell this code
to look at my textbox "Years" to find out which case it falls into?

"Klatuu" wrote:

Assuming it is not a bound field (and it should not be, because it violates
the rule on storing calculated values), you can put it in the Control Source
property for the text box. To ensure the values needed are available, see
the modification below:
(Also I noticed the function had a missing line of code, so it would never
return anything

Function VacHoursEarned(varYears AS Variant) As Integer

If IsNull(varYears) Then
VacHoursEarned = Null
Exit Function
End If
Select Case varYears
Case is 1
VacHoursEarned = 40
Case is 2 To 6
VacHoursEarned = 80
Case is 7 To 11
VacHoursEarned = 120
Case is =12
VacHoursEarned = 160
End Select
End Function


"Secret Squirrel" wrote:

How do I call this function from the textbox where I want it to populate the
hours earned?

"Klatuu" wrote:

If you want to use the value in a text box, just pass the value in the text
box as the function's argument:
VacHoursEarned(Me.txtYrsService)

"Secret Squirrel" wrote:

Hi,
I want to use your function to determine the Vacation hours earned but I'm
unsure how to have it look at the textbox on my form to determine which case
it should use. Can you help shed some light on how I would set this up? I
have a form with a textbox on it that calculates a persons time of employment
from their hiredate until the present date. Now I want the data in that field
to be compared to the select case function you wrote below and have it
populate the hours earned into another textbox.
Thanks
SS

"Klatuu" wrote:

Do you have a field that shows how much time has been taken for the current
year?
There is probably a little complexity here in that your vacation allotments
are probably calculated on an employee's original hire date, but what time is
actually used is calculated on a calendar date (Jan 1 - Dec 31).

In any case, here is a function originally written to determine a person's
age as of a date. It will work for you in determining how long a person has
been employed.

Function CalcAge(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today if
missing.
'Return: Whole number of years.
Dim dtmDOB As Date
Dim dtmAsOf As Date
Dim dtmBDay As Date 'Birthday in the year of calculation.

CalcAge = Null 'Initialize to Null

'Validate parameters
If IsDate(varDOB) Then
dtmDOB = varDOB

If IsDate(varAsOf) Then 'Date to calculate age from.
dtmAsOf = varAsOf
Else
dtmAsOf = date
End If

If dtmAsOf = dtmDOB Then 'Calculate only if it's after person was
born.
dtmBDay = DateSerial(year(dtmAsOf), Month(dtmDOB), Day(dtmDOB))
CalcAge = DateDiff("yyyy", dtmDOB, dtmAsOf) + (dtmBDay dtmAsOf)
Else: CalcAge = Null 'Return Null if AsOf is before birthday
End If
End If
End Function

You can determine how many hours they are eligible for with this:

Function VacHoursEarned(intYears AS integer) As Integer

Select Case intYears
Case is 1
intYears = 40
Case is 2 To 6
intYears = 80
Case is 7 To 11
intYears = 120
Case is =12
intYears = 160
End Select
End Function




"Julie" wrote:

I have set up a table to look up for each person. Here is an example
1 year - 40 hrs
2 years - 80 hrs
7 years - 120 hrs
12 years - 160 hrs.

I have designed a form that inputs everyone's vacation for the year. I just
don't know how to get access to look at what time they have taken and deduct
it from what they were awarded for that year.

"Klatuu" wrote:

Need more information.
How do you know how much vacation an employee is eligible for?
How do you know how much they have used in the current year?

"Julie" wrote:

What I need to figure out now is how to get a query to look at their
anniversary date and add in what amount they get awarded and also to deduct
what they have taken to date. When a new employee is eligible for vacation is
on their anniversary date and then they can start taking it. So, if someone
starts in march they have to wait until the following march to use their
vacation. Can anyone help?