Date Calculation for years of Service

[DWillis]

Attempting to produce a number for Years of Service, by subtracting the field
[hire date]. I am using the following in an unbound text box in a report:
=Date()-[hire date]. It is producing a result, however not recognizeable. I
have the txt box formatted as a general number, so I am getting 3770,
(numbers that don't make sense in actual years). Thoughts?

 

[Jerry Whittle]

If you don't have to be really accurate, as in being a couple of days off
wouldn't hurt:

(Date()-[hire date])/365.5

 

[KARL DEWEY]

It is in days.
Use DateDiff("yyyyy",Date(),[hire date])

BUT, if the hire date was 23 April 2008 it will give you 1 year. If the
hire date was 3 May 2007 it will give you 0 year.

So to get better results you can use DateDiff("m",Date(),[hire
date])/12 or DateDiff("d",Date(),[hire date])/365.25 and so forth.

Search this newsgroup for Age, anniversaries, and birthdays for more ideas.

 

[fredg]

Attempting to produce a number for Years of Service, by subtracting the field
[hire date]. I am using the following in an unbound text box in a report:
=Date()-[hire date]. It is producing a result, however not recognizeable. I
have the txt box formatted as a general number, so I am getting 3770,
(numbers that don't make sense in actual years). Thoughts?

A value of 3770 (in days, which is what your expression calls for) is
about 10 years 4 months.
Access is most likely correctly performing the task you gave it.

To accurately calculate the **Years** of Service, you would use,
directly as the control source of an unbound control on a form or in a
report:

=DateDiff("yyyy",[HireDate],Date())-IIf(Format([HireDate],
"mmdd")>Format(Date(),"mmdd"),1,0)

You do know, I hope, that this Age computation should NOT be stored
in any table. Just compute it and display it on a form or report, as
needed.

 

[DWillis]

My report displays #error in the field when I use this. Is there something
else I need to change? Thank you.

 

[DWillis]

I receive these two results: -- With DateDiff("m",Date(),[hire

date])/12, I receive "enter parameter value (for the hire date).

The 2nd set of code you gave me (DateDiff("d",Date(),[hire date])/365.25 )
is closest however it produces a negative number that is out greater than 10
decimal places. The number is right, but I don't need all the extra on the
report. How to fix?
Thank you,

DWillis

It is in days.
Use DateDiff("yyyyy",Date(),[hire date])

BUT, if the hire date was 23 April 2008 it will give you 1 year. If the
hire date was 3 May 2007 it will give you 0 year.

So to get better results you can use DateDiff("m",Date(),[hire
date])/12 or DateDiff("d",Date(),[hire date])/365.25 and so forth.

Search this newsgroup for Age, anniversaries, and birthdays for more ideas.
--
KARL DEWEY
Build a little - Test a little

Attempting to produce a number for Years of Service, by subtracting the field
[hire date]. I am using the following in an unbound text box in a report:
=Date()-[hire date]. It is producing a result, however not recognizeable. I
have the txt box formatted as a general number, so I am getting 3770,
(numbers that don't make sense in actual years). Thoughts?

 

[DWillis]

Hello,
I am being asked to enter a parameter for the field [Hire Date] when I use
the code you provided.
--
DWillis

Attempting to produce a number for Years of Service, by subtracting the field
[hire date]. I am using the following in an unbound text box in a report:
=Date()-[hire date]. It is producing a result, however not recognizeable. I
have the txt box formatted as a general number, so I am getting 3770,
(numbers that don't make sense in actual years). Thoughts?

A value of 3770 (in days, which is what your expression calls for) is
about 10 years 4 months.
Access is most likely correctly performing the task you gave it.

To accurately calculate the **Years** of Service, you would use,
directly as the control source of an unbound control on a form or in a
report:

=DateDiff("yyyy",[HireDate],Date())-IIf(Format([HireDate],
"mmdd")>Format(Date(),"mmdd"),1,0)

You do know, I hope, that this Age computation should NOT be stored
in any table. Just compute it and display it on a form or report, as
needed.

 

[KARL DEWEY]

Check your typing. Your feedback post has a 'greater than' sign. The 'hire
date' is the name of your field and needs to match it axactly.
To change from the negative number use (DateDiff("d",[hire date],Date())/
365.25 )
Format your display for whatever precision you want.
--
KARL DEWEY
Build a little - Test a little

I receive these two results: -- With DateDiff("m",Date(),[hire

date])/12, I receive "enter parameter value (for the hire date).

The 2nd set of code you gave me (DateDiff("d",Date(),[hire date])/365.25 )
is closest however it produces a negative number that is out greater than 10
decimal places. The number is right, but I don't need all the extra on the
report. How to fix?
Thank you,

DWillis

It is in days.
Use DateDiff("yyyyy",Date(),[hire date])

BUT, if the hire date was 23 April 2008 it will give you 1 year. If the
hire date was 3 May 2007 it will give you 0 year.

So to get better results you can use DateDiff("m",Date(),[hire
date])/12 or DateDiff("d",Date(),[hire date])/365.25 and so forth.

Search this newsgroup for Age, anniversaries, and birthdays for more ideas.
--
KARL DEWEY
Build a little - Test a little

Attempting to produce a number for Years of Service, by subtracting the field
[hire date]. I am using the following in an unbound text box in a report:
=Date()-[hire date]. It is producing a result, however not recognizeable. I
have the txt box formatted as a general number, so I am getting 3770,
(numbers that don't make sense in actual years). Thoughts?

 

[John W. Vinson]

Hello,
I am being asked to enter a parameter for the field [Hire Date] when I use
the code you provided.

Where are you putting the code? In your original post you indicated that there
was a field named [hire date]:

Attempting to produce a number for Years of Service, by subtracting the field
[hire date].

Is that in fact the fieldname in this query, or is the field named something
else? Remember - YOU can see your database; we cannot!

 

[fredg]

Hello,
I am being asked to enter a parameter for the field [Hire Date] when I use
the code you provided.

Do you have a field named "Hire Date"? Evidently not.
Note I used "HireDate" (without the space).
Anyway, replace [Hire Date] or [HireDate] with whatever the actual
name of the field is that contains the date the employee was hired.
make sure it's spelled correctly.

Also make sure the name of this control is not the same as the name of
any field used in it's control source expression.

 

[DWillis]

Hello,

Yes this works better. The result for years appears exactly as of today or
whenever I run the report. However, is there a way to forecast ahead so I
could run the report for 2008, and the manager would see one whole number,
for instance, that in August, Joe Smith will be here for 11 years, and we
wouldn't have to round up the number?
Thank you again.--

DWillis

Check your typing. Your feedback post has a 'greater than' sign. The 'hire
date' is the name of your field and needs to match it axactly.
To change from the negative number use (DateDiff("d",[hire date],Date())/
365.25 )
Format your display for whatever precision you want.
--
KARL DEWEY
Build a little - Test a little

I receive these two results: -- With DateDiff("m",Date(),[hire

date])/12, I receive "enter parameter value (for the hire date).

The 2nd set of code you gave me (DateDiff("d",Date(),[hire date])/365.25 )
is closest however it produces a negative number that is out greater than 10
decimal places. The number is right, but I don't need all the extra on the
report. How to fix?
Thank you,

DWillis

It is in days.
Use DateDiff("yyyyy",Date(),[hire date])

BUT, if the hire date was 23 April 2008 it will give you 1 year. If the
hire date was 3 May 2007 it will give you 0 year.

So to get better results you can use DateDiff("m",Date(),[hire
date])/12 or DateDiff("d",Date(),[hire date])/365.25 and so forth.

Search this newsgroup for Age, anniversaries, and birthdays for more ideas.
--
KARL DEWEY
Build a little - Test a little


:

Attempting to produce a number for Years of Service, by subtracting the field
[hire date]. I am using the following in an unbound text box in a report:
=Date()-[hire date]. It is producing a result, however not recognizeable. I
have the txt box formatted as a general number, so I am getting 3770,
(numbers that don't make sense in actual years). Thoughts?

 

[KARL DEWEY]

You can replace Date() with a prompt for a date --- [Enter as of date
- 08/22/2007] and it will be be based on that date entered.
--
KARL DEWEY
Build a little - Test a little

Hello,

Yes this works better. The result for years appears exactly as of today or
whenever I run the report. However, is there a way to forecast ahead so I
could run the report for 2008, and the manager would see one whole number,
for instance, that in August, Joe Smith will be here for 11 years, and we
wouldn't have to round up the number?
Thank you again.--

DWillis

Check your typing. Your feedback post has a 'greater than' sign. The 'hire
date' is the name of your field and needs to match it axactly.
To change from the negative number use (DateDiff("d",[hire date],Date())/
365.25 )
Format your display for whatever precision you want.
--
KARL DEWEY
Build a little - Test a little

I receive these two results: -- With DateDiff("m",Date(),[hire
date])/12, I receive "enter parameter value (for the hire date).

The 2nd set of code you gave me (DateDiff("d",Date(),[hire date])/365.25 )
is closest however it produces a negative number that is out greater than 10
decimal places. The number is right, but I don't need all the extra on the
report. How to fix?
Thank you,

DWillis


:

It is in days.
Use DateDiff("yyyyy",Date(),[hire date])

BUT, if the hire date was 23 April 2008 it will give you 1 year. If the
hire date was 3 May 2007 it will give you 0 year.

So to get better results you can use DateDiff("m",Date(),[hire
date])/12 or DateDiff("d",Date(),[hire date])/365.25 and so forth.

Search this newsgroup for Age, anniversaries, and birthdays for more ideas.
--
KARL DEWEY
Build a little - Test a little


:

Attempting to produce a number for Years of Service, by subtracting the field
[hire date]. I am using the following in an unbound text box in a report:
=Date()-[hire date]. It is producing a result, however not recognizeable. I
have the txt box formatted as a general number, so I am getting 3770,
(numbers that don't make sense in actual years). Thoughts?