Vacation Query

Ok I set it up but now I'm getting a "syntax error" in the function code.
It's turning the Case lines to red.

"Klatuu" wrote:

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?

Sorry Squirrel, I did not test the code before, I have now, and made a
change. If you pass it anything not in the list, it returns Null.

Function VacHoursEarned(varYears As Variant) As Variant

Select Case varYears
Case Is = 1
VacHoursEarned = 40
Case 2 To 6
VacHoursEarned = 80
Case 7 To 11
VacHoursEarned = 120
Case Is = 12
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function


"Secret Squirrel" wrote:

Ok I set it up but now I'm getting a "syntax error" in the function code.
It's turning the Case lines to red.

"Klatuu" wrote:

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?

ok now it's working perfectly!
But I have 2 follow up questions...

1. If there is no value in my textbox "Years" then it returns an #Error in
the "HoursAccrued" textbox (This is the textbox that is calling the
function). How can I make that just say "0" instead of the error?

2. Can the case lines where the years are defined be pulled from a table?
This way if things were to change I could easily just change them in the
table instead of hard coding them into the function.

"Klatuu" wrote:

Sorry Squirrel, I did not test the code before, I have now, and made a
change. If you pass it anything not in the list, it returns Null.

Function VacHoursEarned(varYears As Variant) As Variant

Select Case varYears
Case Is = 1
VacHoursEarned = 40
Case 2 To 6
VacHoursEarned = 80
Case 7 To 11
VacHoursEarned = 120
Case Is = 12
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function


"Secret Squirrel" wrote:

Ok I set it up but now I'm getting a "syntax error" in the function code.
It's turning the Case lines to red.

"Klatuu" wrote:

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?

If you are getting the error, you may try changing it so it returns 0 instead
of Null where it now returns Null.

Yes, you could modify it to pull the data from a table. Without examining
it in detail, my first thought would be to write a Class module that would
open the table and load the values into variables that become properties of
the class. It would make the function remain clean because you can change
the hard coded values to the property that would represent the years of
service.

Now, if you also want to be able to change the years of service requirements
without chaning the code, you could use the same approach.

"Secret Squirrel" wrote:

ok now it's working perfectly!
But I have 2 follow up questions...

1. If there is no value in my textbox "Years" then it returns an #Error in
the "HoursAccrued" textbox (This is the textbox that is calling the
function). How can I make that just say "0" instead of the error?

2. Can the case lines where the years are defined be pulled from a table?
This way if things were to change I could easily just change them in the
table instead of hard coding them into the function.

"Klatuu" wrote:

Sorry Squirrel, I did not test the code before, I have now, and made a
change. If you pass it anything not in the list, it returns Null.

Function VacHoursEarned(varYears As Variant) As Variant

Select Case varYears
Case Is = 1
VacHoursEarned = 40
Case 2 To 6
VacHoursEarned = 80
Case 7 To 11
VacHoursEarned = 120
Case Is = 12
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function


"Secret Squirrel" wrote:

Ok I set it up but now I'm getting a "syntax error" in the function code.
It's turning the Case lines to red.

"Klatuu" wrote:

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?

I tried to change it from "Null" to 0 but it still returned the #Error. Could
it be something with the format of that control?

I wouldn't even know where to begin to write a class module. VB is not my
strongest point right now. Would it be possible to give me a base line to
start with?

"Klatuu" wrote:

If you are getting the error, you may try changing it so it returns 0 instead
of Null where it now returns Null.

Yes, you could modify it to pull the data from a table. Without examining
it in detail, my first thought would be to write a Class module that would
open the table and load the values into variables that become properties of
the class. It would make the function remain clean because you can change
the hard coded values to the property that would represent the years of
service.

Now, if you also want to be able to change the years of service requirements
without chaning the code, you could use the same approach.

"Secret Squirrel" wrote:

ok now it's working perfectly!
But I have 2 follow up questions...

1. If there is no value in my textbox "Years" then it returns an #Error in
the "HoursAccrued" textbox (This is the textbox that is calling the
function). How can I make that just say "0" instead of the error?

2. Can the case lines where the years are defined be pulled from a table?
This way if things were to change I could easily just change them in the
table instead of hard coding them into the function.

"Klatuu" wrote:

Sorry Squirrel, I did not test the code before, I have now, and made a
change. If you pass it anything not in the list, it returns Null.

Function VacHoursEarned(varYears As Variant) As Variant

Select Case varYears
Case Is = 1
VacHoursEarned = 40
Case 2 To 6
VacHoursEarned = 80
Case 7 To 11
VacHoursEarned = 120
Case Is = 12
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function


"Secret Squirrel" wrote:

Ok I set it up but now I'm getting a "syntax error" in the function code.
It's turning the Case lines to red.

"Klatuu" wrote:

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?

Are you saying that if the Years text box has a valid value, it returns the
correct value, but if Years is empty or is a value outside the range of the
function, it returns #Error?

As to Class modules. They are easier than you think. I was intimidated by
them for a long time. This is one I use. We have a lot of information our
system used based on where we are in the accounting calander. Our accounting
periods are not based on the month. Some have 4 weeks, some 5 and some 6. I
was having to do a lot of DLookups and coding in my forms and reports to get
the correct information. So I came up with this. It uses a one row table
that tells me what the date the current accounting period ends on. I then
use the Initialize Event to read the tables and load the data into
varialbes. Then the Property Get statements return those values.
The name of the module is cisDateValues. Class modules are different from
standard modules in that the name of the module is significant. It becomes
the name of the class, so to set a reference to it:

Set clsDates = New cisDateVaules

Then if I need one of the propertys, say the number of hours in the current
week:

= clsDates.HoursInWeek

'---------------------------------------------------------------------------------------
' Module : cisDateValues
' DateTime : 7/28/2006 10:08
' Author : Dave Hargis
' Purpose : Returns information from tbllkacct table
'---------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Option Base 0

'Data for the month
Private intCurrMonth As Integer
Private intCurrYear As Integer
Private intWeeksInMonth As Integer
Private intMonthHours As Integer
Private dtmMonthEndDate As Date
'Current Week Info
Private dtmWeekEndDate As Date
Private intWeekNumber As Integer
Private intHoursInWeek As Integer
Private intHoursMTD As Integer
Private intHoursYTD As Integer
Private intHoursRemaining As Integer

Property Get CurrentMonth() As Integer
CurrentMonth = intCurrMonth
End Property

Property Get CurrentYear() As Integer
CurrentYear = intCurrYear
End Property
Property Get WeeksInMonth() As Integer
WeeksInMonth = intWeeksInMonth
End Property

Property Get MonthHours() As Integer
MonthHours = intMonthHours
End Property

Property Get MonthEndDate() As Date
MonthEndDate = dtmMonthEndDate
End Property

Property Get WeekEndDate() As Date
dtmWeekEndDate = dtmWeekEndDate
End Property

Property Get WeekNumber() As Integer
WeekNumber = intWeekNumber
End Property

Property Get HoursInWeek() As Integer
HoursInWeek = intHoursInWeek
End Property

Property Get HoursMTD() As Integer
HoursMTD = intHoursMTD
End Property

Property Get HoursYTD() As Integer
HoursYTD = intHoursYTD
End Property

Property Get HoursRemaining() As Integer
HoursRemaining = intHoursRemaining
End Property

Private Sub Class_Initialize()
Dim rst As Recordset

'Get Current Period Info
dtmMonthEndDate = DLookup("[wedate]", "tblwedate")
Set rst = CurrentDb.OpenRecordset("tbllkacctdate", dbOpenDynaset)
rst.FindFirst "[acctwedate] = #" & dtmMonthEndDate & "#"
intCurrMonth = CInt(rst![mon])
intCurrYear = year(rst![acctmon])
intWeeksInMonth = rst![WksPerMon]
intMonthHours = rst![HrsPerMonth]
dtmMonthEndDate = rst![acctmon]

'Get Current Week INfo
rst.FindFirst "[acctwedate] = #" & DateAdd("d", vbFriday - DatePart("w",
date), _
date) & "#"
dtmWeekEndDate = rst![AcctWedate]
intWeekNumber = rst![MonWkNum]
intHoursInWeek = rst![WeekHours]
intHoursMTD = rst![mtdhrs]
intHoursYTD = rst![YtdHrs]
intHoursRemaining = rst![remaining hrs]

rst.Close
Set rst = Nothing

End Sub




"Secret Squirrel" wrote:

I tried to change it from "Null" to 0 but it still returned the #Error. Could
it be something with the format of that control?

I wouldn't even know where to begin to write a class module. VB is not my
strongest point right now. Would it be possible to give me a base line to
start with?

"Klatuu" wrote:

If you are getting the error, you may try changing it so it returns 0 instead
of Null where it now returns Null.

Yes, you could modify it to pull the data from a table. Without examining
it in detail, my first thought would be to write a Class module that would
open the table and load the values into variables that become properties of
the class. It would make the function remain clean because you can change
the hard coded values to the property that would represent the years of
service.

Now, if you also want to be able to change the years of service requirements
without chaning the code, you could use the same approach.

"Secret Squirrel" wrote:

ok now it's working perfectly!
But I have 2 follow up questions...

1. If there is no value in my textbox "Years" then it returns an #Error in
the "HoursAccrued" textbox (This is the textbox that is calling the
function). How can I make that just say "0" instead of the error?

2. Can the case lines where the years are defined be pulled from a table?
This way if things were to change I could easily just change them in the
table instead of hard coding them into the function.

"Klatuu" wrote:

Sorry Squirrel, I did not test the code before, I have now, and made a
change. If you pass it anything not in the list, it returns Null.

Function VacHoursEarned(varYears As Variant) As Variant

Select Case varYears
Case Is = 1
VacHoursEarned = 40
Case 2 To 6
VacHoursEarned = 80
Case 7 To 11
VacHoursEarned = 120
Case Is = 12
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function


"Secret Squirrel" wrote:

Ok I set it up but now I'm getting a "syntax error" in the function code.
It's turning the Case lines to red.

"Klatuu" wrote:

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?

Yes that is correct. I have a record that has no value in the Years textbox
and it returns that #Error.

I will tinker with the class module tonight.

"Klatuu" wrote:

Are you saying that if the Years text box has a valid value, it returns the
correct value, but if Years is empty or is a value outside the range of the
function, it returns #Error?

As to Class modules. They are easier than you think. I was intimidated by
them for a long time. This is one I use. We have a lot of information our
system used based on where we are in the accounting calander. Our accounting
periods are not based on the month. Some have 4 weeks, some 5 and some 6. I
was having to do a lot of DLookups and coding in my forms and reports to get
the correct information. So I came up with this. It uses a one row table
that tells me what the date the current accounting period ends on. I then
use the Initialize Event to read the tables and load the data into
varialbes. Then the Property Get statements return those values.
The name of the module is cisDateValues. Class modules are different from
standard modules in that the name of the module is significant. It becomes
the name of the class, so to set a reference to it:

Set clsDates = New cisDateVaules

Then if I need one of the propertys, say the number of hours in the current
week:

= clsDates.HoursInWeek

'---------------------------------------------------------------------------------------
' Module : cisDateValues
' DateTime : 7/28/2006 10:08
' Author : Dave Hargis
' Purpose : Returns information from tbllkacct table
'---------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Option Base 0

'Data for the month
Private intCurrMonth As Integer
Private intCurrYear As Integer
Private intWeeksInMonth As Integer
Private intMonthHours As Integer
Private dtmMonthEndDate As Date
'Current Week Info
Private dtmWeekEndDate As Date
Private intWeekNumber As Integer
Private intHoursInWeek As Integer
Private intHoursMTD As Integer
Private intHoursYTD As Integer
Private intHoursRemaining As Integer

Property Get CurrentMonth() As Integer
CurrentMonth = intCurrMonth
End Property

Property Get CurrentYear() As Integer
CurrentYear = intCurrYear
End Property
Property Get WeeksInMonth() As Integer
WeeksInMonth = intWeeksInMonth
End Property

Property Get MonthHours() As Integer
MonthHours = intMonthHours
End Property

Property Get MonthEndDate() As Date
MonthEndDate = dtmMonthEndDate
End Property

Property Get WeekEndDate() As Date
dtmWeekEndDate = dtmWeekEndDate
End Property

Property Get WeekNumber() As Integer
WeekNumber = intWeekNumber
End Property

Property Get HoursInWeek() As Integer
HoursInWeek = intHoursInWeek
End Property

Property Get HoursMTD() As Integer
HoursMTD = intHoursMTD
End Property

Property Get HoursYTD() As Integer
HoursYTD = intHoursYTD
End Property

Property Get HoursRemaining() As Integer
HoursRemaining = intHoursRemaining
End Property

Private Sub Class_Initialize()
Dim rst As Recordset

'Get Current Period Info
dtmMonthEndDate = DLookup("[wedate]", "tblwedate")
Set rst = CurrentDb.OpenRecordset("tbllkacctdate", dbOpenDynaset)
rst.FindFirst "[acctwedate] = #" & dtmMonthEndDate & "#"
intCurrMonth = CInt(rst![mon])
intCurrYear = year(rst![acctmon])
intWeeksInMonth = rst![WksPerMon]
intMonthHours = rst![HrsPerMonth]
dtmMonthEndDate = rst![acctmon]

'Get Current Week INfo
rst.FindFirst "[acctwedate] = #" & DateAdd("d", vbFriday - DatePart("w",
date), _
date) & "#"
dtmWeekEndDate = rst![AcctWedate]
intWeekNumber = rst![MonWkNum]
intHoursInWeek = rst![WeekHours]
intHoursMTD = rst![mtdhrs]
intHoursYTD = rst![YtdHrs]
intHoursRemaining = rst![remaining hrs]

rst.Close
Set rst = Nothing

End Sub




"Secret Squirrel" wrote:

I tried to change it from "Null" to 0 but it still returned the #Error. Could
it be something with the format of that control?

I wouldn't even know where to begin to write a class module. VB is not my
strongest point right now. Would it be possible to give me a base line to
start with?

"Klatuu" wrote:

If you are getting the error, you may try changing it so it returns 0 instead
of Null where it now returns Null.

Yes, you could modify it to pull the data from a table. Without examining
it in detail, my first thought would be to write a Class module that would
open the table and load the values into variables that become properties of
the class. It would make the function remain clean because you can change
the hard coded values to the property that would represent the years of
service.

Now, if you also want to be able to change the years of service requirements
without chaning the code, you could use the same approach.

"Secret Squirrel" wrote:

ok now it's working perfectly!
But I have 2 follow up questions...

1. If there is no value in my textbox "Years" then it returns an #Error in
the "HoursAccrued" textbox (This is the textbox that is calling the
function). How can I make that just say "0" instead of the error?

2. Can the case lines where the years are defined be pulled from a table?
This way if things were to change I could easily just change them in the
table instead of hard coding them into the function.

"Klatuu" wrote:

Sorry Squirrel, I did not test the code before, I have now, and made a
change. If you pass it anything not in the list, it returns Null.

Function VacHoursEarned(varYears As Variant) As Variant

Select Case varYears
Case Is = 1
VacHoursEarned = 40
Case 2 To 6
VacHoursEarned = 80
Case 7 To 11
VacHoursEarned = 120
Case Is = 12
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function


"Secret Squirrel" wrote:

Ok I set it up but now I'm getting a "syntax error" in the function code.
It's turning the Case lines to red.

"Klatuu" wrote:

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

I will have to do some more testing. When I ran it from the immediate
window, I passed it Null, Negative values, 0, and real values and always got
the expected results. I will post back tomorrow on this, I am in the middle
of a really complicated report right now.

"Secret Squirrel" wrote:

Yes that is correct. I have a record that has no value in the Years textbox
and it returns that #Error.

I will tinker with the class module tonight.

"Klatuu" wrote:

Are you saying that if the Years text box has a valid value, it returns the
correct value, but if Years is empty or is a value outside the range of the
function, it returns #Error?

As to Class modules. They are easier than you think. I was intimidated by
them for a long time. This is one I use. We have a lot of information our
system used based on where we are in the accounting calander. Our accounting
periods are not based on the month. Some have 4 weeks, some 5 and some 6. I
was having to do a lot of DLookups and coding in my forms and reports to get
the correct information. So I came up with this. It uses a one row table
that tells me what the date the current accounting period ends on. I then
use the Initialize Event to read the tables and load the data into
varialbes. Then the Property Get statements return those values.
The name of the module is cisDateValues. Class modules are different from
standard modules in that the name of the module is significant. It becomes
the name of the class, so to set a reference to it:

Set clsDates = New cisDateVaules

Then if I need one of the propertys, say the number of hours in the current
week:

= clsDates.HoursInWeek

'---------------------------------------------------------------------------------------
' Module : cisDateValues
' DateTime : 7/28/2006 10:08
' Author : Dave Hargis
' Purpose : Returns information from tbllkacct table
'---------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Option Base 0

'Data for the month
Private intCurrMonth As Integer
Private intCurrYear As Integer
Private intWeeksInMonth As Integer
Private intMonthHours As Integer
Private dtmMonthEndDate As Date
'Current Week Info
Private dtmWeekEndDate As Date
Private intWeekNumber As Integer
Private intHoursInWeek As Integer
Private intHoursMTD As Integer
Private intHoursYTD As Integer
Private intHoursRemaining As Integer

Property Get CurrentMonth() As Integer
CurrentMonth = intCurrMonth
End Property

Property Get CurrentYear() As Integer
CurrentYear = intCurrYear
End Property
Property Get WeeksInMonth() As Integer
WeeksInMonth = intWeeksInMonth
End Property

Property Get MonthHours() As Integer
MonthHours = intMonthHours
End Property

Property Get MonthEndDate() As Date
MonthEndDate = dtmMonthEndDate
End Property

Property Get WeekEndDate() As Date
dtmWeekEndDate = dtmWeekEndDate
End Property

Property Get WeekNumber() As Integer
WeekNumber = intWeekNumber
End Property

Property Get HoursInWeek() As Integer
HoursInWeek = intHoursInWeek
End Property

Property Get HoursMTD() As Integer
HoursMTD = intHoursMTD
End Property

Property Get HoursYTD() As Integer
HoursYTD = intHoursYTD
End Property

Property Get HoursRemaining() As Integer
HoursRemaining = intHoursRemaining
End Property

Private Sub Class_Initialize()
Dim rst As Recordset

'Get Current Period Info
dtmMonthEndDate = DLookup("[wedate]", "tblwedate")
Set rst = CurrentDb.OpenRecordset("tbllkacctdate", dbOpenDynaset)
rst.FindFirst "[acctwedate] = #" & dtmMonthEndDate & "#"
intCurrMonth = CInt(rst![mon])
intCurrYear = year(rst![acctmon])
intWeeksInMonth = rst![WksPerMon]
intMonthHours = rst![HrsPerMonth]
dtmMonthEndDate = rst![acctmon]

'Get Current Week INfo
rst.FindFirst "[acctwedate] = #" & DateAdd("d", vbFriday - DatePart("w",
date), _
date) & "#"
dtmWeekEndDate = rst![AcctWedate]
intWeekNumber = rst![MonWkNum]
intHoursInWeek = rst![WeekHours]
intHoursMTD = rst![mtdhrs]
intHoursYTD = rst![YtdHrs]
intHoursRemaining = rst![remaining hrs]

rst.Close
Set rst = Nothing

End Sub




"Secret Squirrel" wrote:

I tried to change it from "Null" to 0 but it still returned the #Error. Could
it be something with the format of that control?

I wouldn't even know where to begin to write a class module. VB is not my
strongest point right now. Would it be possible to give me a base line to
start with?

"Klatuu" wrote:

If you are getting the error, you may try changing it so it returns 0 instead
of Null where it now returns Null.

Yes, you could modify it to pull the data from a table. Without examining
it in detail, my first thought would be to write a Class module that would
open the table and load the values into variables that become properties of
the class. It would make the function remain clean because you can change
the hard coded values to the property that would represent the years of
service.

Now, if you also want to be able to change the years of service requirements
without chaning the code, you could use the same approach.

"Secret Squirrel" wrote:

ok now it's working perfectly!
But I have 2 follow up questions...

1. If there is no value in my textbox "Years" then it returns an #Error in
the "HoursAccrued" textbox (This is the textbox that is calling the
function). How can I make that just say "0" instead of the error?

2. Can the case lines where the years are defined be pulled from a table?
This way if things were to change I could easily just change them in the
table instead of hard coding them into the function.

"Klatuu" wrote:

Sorry Squirrel, I did not test the code before, I have now, and made a
change. If you pass it anything not in the list, it returns Null.

Function VacHoursEarned(varYears As Variant) As Variant

Select Case varYears
Case Is = 1
VacHoursEarned = 40
Case 2 To 6
VacHoursEarned = 80
Case 7 To 11
VacHoursEarned = 120
Case Is = 12
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function


"Secret Squirrel" wrote:

Ok I set it up but now I'm getting a "syntax error" in the function code.
It's turning the Case lines to red.

"Klatuu" wrote:

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

Ok thanks!

"Klatuu" wrote:

I will have to do some more testing. When I ran it from the immediate
window, I passed it Null, Negative values, 0, and real values and always got
the expected results. I will post back tomorrow on this, I am in the middle
of a really complicated report right now.

"Secret Squirrel" wrote:

Yes that is correct. I have a record that has no value in the Years textbox
and it returns that #Error.

I will tinker with the class module tonight.

"Klatuu" wrote:

Are you saying that if the Years text box has a valid value, it returns the
correct value, but if Years is empty or is a value outside the range of the
function, it returns #Error?

As to Class modules. They are easier than you think. I was intimidated by
them for a long time. This is one I use. We have a lot of information our
system used based on where we are in the accounting calander. Our accounting
periods are not based on the month. Some have 4 weeks, some 5 and some 6. I
was having to do a lot of DLookups and coding in my forms and reports to get
the correct information. So I came up with this. It uses a one row table
that tells me what the date the current accounting period ends on. I then
use the Initialize Event to read the tables and load the data into
varialbes. Then the Property Get statements return those values.
The name of the module is cisDateValues. Class modules are different from
standard modules in that the name of the module is significant. It becomes
the name of the class, so to set a reference to it:

Set clsDates = New cisDateVaules

Then if I need one of the propertys, say the number of hours in the current
week:

= clsDates.HoursInWeek

'---------------------------------------------------------------------------------------
' Module : cisDateValues
' DateTime : 7/28/2006 10:08
' Author : Dave Hargis
' Purpose : Returns information from tbllkacct table
'---------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Option Base 0

'Data for the month
Private intCurrMonth As Integer
Private intCurrYear As Integer
Private intWeeksInMonth As Integer
Private intMonthHours As Integer
Private dtmMonthEndDate As Date
'Current Week Info
Private dtmWeekEndDate As Date
Private intWeekNumber As Integer
Private intHoursInWeek As Integer
Private intHoursMTD As Integer
Private intHoursYTD As Integer
Private intHoursRemaining As Integer

Property Get CurrentMonth() As Integer
CurrentMonth = intCurrMonth
End Property

Property Get CurrentYear() As Integer
CurrentYear = intCurrYear
End Property
Property Get WeeksInMonth() As Integer
WeeksInMonth = intWeeksInMonth
End Property

Property Get MonthHours() As Integer
MonthHours = intMonthHours
End Property

Property Get MonthEndDate() As Date
MonthEndDate = dtmMonthEndDate
End Property

Property Get WeekEndDate() As Date
dtmWeekEndDate = dtmWeekEndDate
End Property

Property Get WeekNumber() As Integer
WeekNumber = intWeekNumber
End Property

Property Get HoursInWeek() As Integer
HoursInWeek = intHoursInWeek
End Property

Property Get HoursMTD() As Integer
HoursMTD = intHoursMTD
End Property

Property Get HoursYTD() As Integer
HoursYTD = intHoursYTD
End Property

Property Get HoursRemaining() As Integer
HoursRemaining = intHoursRemaining
End Property

Private Sub Class_Initialize()
Dim rst As Recordset

'Get Current Period Info
dtmMonthEndDate = DLookup("[wedate]", "tblwedate")
Set rst = CurrentDb.OpenRecordset("tbllkacctdate", dbOpenDynaset)
rst.FindFirst "[acctwedate] = #" & dtmMonthEndDate & "#"
intCurrMonth = CInt(rst![mon])
intCurrYear = year(rst![acctmon])
intWeeksInMonth = rst![WksPerMon]
intMonthHours = rst![HrsPerMonth]
dtmMonthEndDate = rst![acctmon]

'Get Current Week INfo
rst.FindFirst "[acctwedate] = #" & DateAdd("d", vbFriday - DatePart("w",
date), _
date) & "#"
dtmWeekEndDate = rst![AcctWedate]
intWeekNumber = rst![MonWkNum]
intHoursInWeek = rst![WeekHours]
intHoursMTD = rst![mtdhrs]
intHoursYTD = rst![YtdHrs]
intHoursRemaining = rst![remaining hrs]

rst.Close
Set rst = Nothing

End Sub




"Secret Squirrel" wrote:

I tried to change it from "Null" to 0 but it still returned the #Error. Could
it be something with the format of that control?

I wouldn't even know where to begin to write a class module. VB is not my
strongest point right now. Would it be possible to give me a base line to
start with?

"Klatuu" wrote:

If you are getting the error, you may try changing it so it returns 0 instead
of Null where it now returns Null.

Yes, you could modify it to pull the data from a table. Without examining
it in detail, my first thought would be to write a Class module that would
open the table and load the values into variables that become properties of
the class. It would make the function remain clean because you can change
the hard coded values to the property that would represent the years of
service.

Now, if you also want to be able to change the years of service requirements
without chaning the code, you could use the same approach.

"Secret Squirrel" wrote:

ok now it's working perfectly!
But I have 2 follow up questions...

1. If there is no value in my textbox "Years" then it returns an #Error in
the "HoursAccrued" textbox (This is the textbox that is calling the
function). How can I make that just say "0" instead of the error?

2. Can the case lines where the years are defined be pulled from a table?
This way if things were to change I could easily just change them in the
table instead of hard coding them into the function.

"Klatuu" wrote:

Sorry Squirrel, I did not test the code before, I have now, and made a
change. If you pass it anything not in the list, it returns Null.

Function VacHoursEarned(varYears As Variant) As Variant

Select Case varYears
Case Is = 1
VacHoursEarned = 40
Case 2 To 6
VacHoursEarned = 80
Case 7 To 11
VacHoursEarned = 120
Case Is = 12
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function


"Secret Squirrel" wrote:

Ok I set it up but now I'm getting a "syntax error" in the function code.
It's turning the Case lines to red.

"Klatuu" wrote:

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