Working with dates

[Afrosheen via AccessMonster.com]

Thanks for reading this. This is what I need to do.

I have a field called "Hire Date". What I need to do is to calculate that
date out to about 20 years and call it "Retirement Date"

Using the same field, "Hire Date" I have a vested field called "Date Vested".
With this on you're vested either by 5years or 10 years. For example: If your
hire date is < Jan. 5, 2001 then your vested rights would be 5 years. If
your hire date is > October 1, 2001 your vested right is 10 years.

Thanks for the help.

 

[David H]

Thanks for reading this. This is what I need to do.

I have a field called "Hire Date". What I need to do is to calculate that
date out to about 20 years and call it "Retirement Date"

DateAdd("y",20,[HireDate])

That being said, calculated values such as Retirement Date shouldn't be
stored in the database. Rather, they should be calculated on the fly. One
example would be to set an unbound control's control source on a form to
=DateAdd("y",20,[HireDate]) where HireDate is the name of the control or
field that contains the HireDate.

Using the same field, "Hire Date" I have a vested field called "Date Vested".
With this on you're vested either by 5years or 10 years. For example: If your
hire date is < Jan. 5, 2001 then your vested rights would be 5 years. If
your hire date is > October 1, 2001 your vested right is 10 years.

Thanks for the help.

Same concept, don't store the actual date. In this instance you'd save the
number of years required for the employee to be vested and then calculate the
date from there. In this instance...

=DateAdd("d",[YearsRequired],[HireDate]) where [YearsRequired] represented
the number years until the person is vested.

Post back if you need help from there.

 

[John W. Vinson]

Thanks for reading this. This is what I need to do.

I have a field called "Hire Date". What I need to do is to calculate that
date out to about 20 years and call it "Retirement Date"

Ummm... suppose you hire someone who's 58 years old when she's hired? Will you
insist that she not retire until years after she reaches 65?

Using the same field, "Hire Date" I have a vested field called "Date Vested".
With this on you're vested either by 5years or 10 years. For example: If your
hire date is < Jan. 5, 2001 then your vested rights would be 5 years. If
your hire date is > October 1, 2001 your vested right is 10 years.

Thanks for the help.

DateAdd("yyyy", <number of years>, [Hire Date]) will get your calculated date.
I agree with David that this should be calculated and not stored, unless (as
noted above) you want to be able to edit the retirement or vested date
independent of the actual hire date.

 

[fredg]

Thanks for reading this. This is what I need to do.

I have a field called "Hire Date". What I need to do is to calculate that
date out to about 20 years and call it "Retirement Date"

DateAdd("y",20,[HireDate])

That being said, calculated values such as Retirement Date shouldn't be
stored in the database. Rather, they should be calculated on the fly. One
example would be to set an unbound control's control source on a form to
=DateAdd("y",20,[HireDate]) where HireDate is the name of the control or
field that contains the HireDate.

Using the same field, "Hire Date" I have a vested field called "Date Vested".
With this on you're vested either by 5years or 10 years. For example: If your
hire date is < Jan. 5, 2001 then your vested rights would be 5 years. If
your hire date is > October 1, 2001 your vested right is 10 years.

Thanks for the help.

Same concept, don't store the actual date. In this instance you'd save the
number of years required for the employee to be vested and then calculate the
date from there. In this instance...

=DateAdd("d",[YearsRequired],[HireDate]) where [YearsRequired] represented
the number years until the person is vested.

Post back if you need help from there.

Be careful with DateAdd in regards to years.
Your expression should be:

=DateAdd("yyyy",20,[HireDate])

That's 4 "y's" for years.
One "y" would be for days.

?DateAdd("yyyy",20,Date())
10/10/2029
?DateAdd("y",20,Date())
10/30/2009
See VBA help regarding the various arguments for the DateAdd function.

 

[Afrosheen via AccessMonster.com]

Thanks for all the replies. On the vested date. A couple of years back,
anyone with a hire date before Oct. 1 2006 would have a vested year at 5
years. After Oct.1 it changed to 10 years. So what I wanted to do was to
check the hire date and see if it's before or after Oct. 1 and then add the
appropriate vested years. I think it could be done something like this.

If [Hire Date] > "10/01/2006" then
DateAdd("yyyy", 10, [Hire Date])
else
DateAdd("yyyy", 5, [Hire Date])
endif

I was thinking of storing the vested date because after it is calculated then
it will not change.

John, I guess you're right on the age of the person and the hire date. Like I
was 57 when I was hired, so my retirement would be based on the age and not
the date of hire. But then again if a person is 30, they can retire from the
state in 20 years.

Thanks for the reply.

Thanks for reading this. This is what I need to do.

I have a field called "Hire Date". What I need to do is to calculate that
date out to about 20 years and call it "Retirement Date"

Ummm... suppose you hire someone who's 58 years old when she's hired? Will you
insist that she not retire until years after she reaches 65?

Using the same field, "Hire Date" I have a vested field called "Date Vested".
With this on you're vested either by 5years or 10 years. For example: If your
hire date is < Jan. 5, 2001 then your vested rights would be 5 years. If
your hire date is > October 1, 2001 your vested right is 10 years.

Thanks for the help.

DateAdd("yyyy", <number of years>, [Hire Date]) will get your calculated date.
I agree with David that this should be calculated and not stored, unless (as
noted above) you want to be able to edit the retirement or vested date
independent of the actual hire date.

 

[David H]

For the vested dates, I would go with a table to capture the number of years
required as in...

StartDate EndDate YearsRequired
10/1/1901 9/30/2005 5
10/1/2005 9/30/2010 10
10/1/2010 9/30/2020 7

This will allow the flexibility to easily adapt if the terms change again.
For the current period, you'd just need to set an end date far enough into
the future, such as 30 years to allow the look up to continue somewhat
indefinately.

Thanks for all the replies. On the vested date. A couple of years back,
anyone with a hire date before Oct. 1 2006 would have a vested year at 5
years. After Oct.1 it changed to 10 years. So what I wanted to do was to
check the hire date and see if it's before or after Oct. 1 and then add the
appropriate vested years. I think it could be done something like this.

If [Hire Date] > "10/01/2006" then
DateAdd("yyyy", 10, [Hire Date])
else
DateAdd("yyyy", 5, [Hire Date])
endif

I was thinking of storing the vested date because after it is calculated then
it will not change.

John, I guess you're right on the age of the person and the hire date. Like I
was 57 when I was hired, so my retirement would be based on the age and not
the date of hire. But then again if a person is 30, they can retire from the
state in 20 years.

Thanks for the reply.

Thanks for reading this. This is what I need to do.

I have a field called "Hire Date". What I need to do is to calculate that
date out to about 20 years and call it "Retirement Date"

Ummm... suppose you hire someone who's 58 years old when she's hired? Will you
insist that she not retire until years after she reaches 65?

Using the same field, "Hire Date" I have a vested field called "Date Vested".
With this on you're vested either by 5years or 10 years. For example: If your
hire date is < Jan. 5, 2001 then your vested rights would be 5 years. If
your hire date is > October 1, 2001 your vested right is 10 years.

Thanks for the help.

DateAdd("yyyy", <number of years>, [Hire Date]) will get your calculated date.
I agree with David that this should be calculated and not stored, unless (as
noted above) you want to be able to edit the retirement or vested date
independent of the actual hire date.

 

[Afrosheen via AccessMonster.com]

Thanks Dave for getting back to me so fast.. I set up the table as per your
instructions. Now in order for the vested date to come up would you use the
code:

If [Hire Date] > [EndDate] then
DateAdd("yyyy", [YearsRequired], [Hire Date])
endif

Would this be correct?

BTW, the [HireDate] would be either <> than the [EndDate].

For the vested dates, I would go with a table to capture the number of years
required as in...

StartDate EndDate YearsRequired
10/1/1901 9/30/2005 5
10/1/2005 9/30/2010 10
10/1/2010 9/30/2020 7

This will allow the flexibility to easily adapt if the terms change again.
For the current period, you'd just need to set an end date far enough into
the future, such as 30 years to allow the look up to continue somewhat
indefinately.

Thanks for all the replies. On the vested date. A couple of years back,
anyone with a hire date before Oct. 1 2006 would have a vested year at 5

[quoted text clipped - 39 lines]

 

[David H]

You'd use DLookup() and do the math from there as in...

---
Dim YearsToVestiture as Integer

YearsToVestiture = DLookup("YearsRequired",[TableNameHere],"StartDate =>
[Forms]![FormNameHere]![ControlNameHere] and EndDate <=
[Forms]![FormNameHere]![ControlNameHere]")

VestingDate = DateAdd("yyyy", YearsToVestiture,
[Forms]![FormNameHere]![ControlNameHere]")
---

Where [TableNameHere] is the table with the information,
[FormNameHere] is the name of the form you're displaying the information on
(or report) and [ControlNameHere] is the control on the form (or report) that
displays the HireDate

Note: I can never F***ing remember the syntax for (equal to or greater than)
and (equal to or lesser than). You'll probably have to play with that get it
right.

....and now let me introduce you to Boundary analysis & testing...

To ensure that your logic is working correctly, if you're lookup table has
the following records...

StartDate EndDate YearsRequired
10/1/1950 9/30/2005 5
10/1/2005 9/30/2010 10
10/1/2010 9/30/2020 7

You'll want to do a spot check to ensure that the correct values are
returned. Add the following employees with the following hire dates. If the
code is working properly you should get the results that I noted...

Hire Date Name Result
9/30/1949 Emp 1 Invalid - Access should throw an error. The DLookup
can't find a matching
record since none exists.
10/1/1950 Emp 2 5
10/2/1950 Emp 3 5

9/29/2005 Emp 4 5
9/30/2005 Emp 5 5

10/1/2005 Emp 6 10
10/2/2005 Emp 7 10

9/29/2010 Emp 8 10
9/30/2010 Emp 9 10

10/1/2010 Emp 10 7
10/2/2010 Emp 11 7

9/29/2020 Emp 12 7
9/30/2020 Emp 13 7
10/1/2020 Emp 14 Invalid - As in the first case, the DLookup can't
find a
matching record

Boundary analysis is an approach to testing that basically says if the
values along a boundary return the correct results then it is unneccessary to
test the remaining values unless they occur on another boundary. If you have
a threshold of 100 and 99, 100, and 101 return the correct result, then there
is no need to test any value less than 99 or greater than 101 as the result
will be the same. Technically, in your scenario the test could be conducted
with a single record, but given the total number of possibilities testing all
three is no big deal.

Also, if you have a scenario where two records overlap as in
10/1/1950 9/30/2005 5
9/1/2005 9/30/2010 10
The DLookup will return the first value that it encounters.

Thanks Dave for getting back to me so fast.. I set up the table as per your
instructions. Now in order for the vested date to come up would you use the
code:

If [Hire Date] > [EndDate] then
DateAdd("yyyy", [YearsRequired], [Hire Date])
endif

Would this be correct?

BTW, the [HireDate] would be either <> than the [EndDate].

For the vested dates, I would go with a table to capture the number of years
required as in...

StartDate EndDate YearsRequired
10/1/1901 9/30/2005 5
10/1/2005 9/30/2010 10
10/1/2010 9/30/2020 7

This will allow the flexibility to easily adapt if the terms change again.
For the current period, you'd just need to set an end date far enough into
the future, such as 30 years to allow the look up to continue somewhat
indefinately.

Thanks for all the replies. On the vested date. A couple of years back,
anyone with a hire date before Oct. 1 2006 would have a vested year at 5

[quoted text clipped - 39 lines]

noted above) you want to be able to edit the retirement or vested date
independent of the actual hire date.