IF and DateDiff

[Guest]

On my Form I have a box that automatically fills in with an employees date of
hire (txtdateofhire). I want another box (txtyearsofservice) on my form to
figure out the difference in years from (txtdateofhire) and
(txtdateofinjury). BUT....If there is no date filled in for (txtdateofhire)
then I want (txtyearsofservice) to display "TEMP EMPLOYEE"

Can someone please help me get this. I'm realy not very good at codes so if
you could explain step by step I would really appreciate it. Thank you very
much in advance.

Travis Dahlman

 

[Wayne Morgan]

Set the Control Source of txtYearsOfService to an equation.

=IIf(IsNull([txtDateOfHire]), "TEMP EMPLOYEE", Age([txtDateOfHire]))

The IIf will evaluate the first part and if it is True will return the
second part. If the first part is False, the third part will be returned.
The function for Age can be found here. It is to return a persons Age from
their birthday and today's date, but the result is the same for DateOfHire.

http://www.mvps.org/access/datetime/date0001.htm

 

[fredg]

On my Form I have a box that automatically fills in with an employees date of
hire (txtdateofhire). I want another box (txtyearsofservice) on my form to
figure out the difference in years from (txtdateofhire) and
(txtdateofinjury). BUT....If there is no date filled in for (txtdateofhire)
then I want (txtyearsofservice) to display "TEMP EMPLOYEE"

Can someone please help me get this. I'm realy not very good at codes so if
you could explain step by step I would really appreciate it. Thank you very
much in advance.

Travis Dahlman

As the control source of an UNBOUND control on the form:
=IIf(IsNull([txtdateofhire]),"Temp Employee",
DateDiff("yyyy",[txtdateofhire],[txtdateofinjury]))

NOTE: DateDiff will return the number of different years (not
necessarily full years) between the 2 fields, i.e. the result of
DateDiff("yyyy",#12/31/2004#,#1/1/2005#) is 1, even though the number
of days difference is also 1.

 

[Guest]

I can't get either of your things to work and I've asked this question a few
weeks ago and pretty much got the same answers and they didn't work either.
Could it be because I'm trying to enter "TEMP EMPLOYEE" which is text into a
box defined as Date?

On my Form I have a box that automatically fills in with an employees date of
hire (txtdateofhire). I want another box (txtyearsofservice) on my form to
figure out the difference in years from (txtdateofhire) and
(txtdateofinjury). BUT....If there is no date filled in for (txtdateofhire)
then I want (txtyearsofservice) to display "TEMP EMPLOYEE"

Can someone please help me get this. I'm realy not very good at codes so if
you could explain step by step I would really appreciate it. Thank you very
much in advance.

Travis Dahlman

As the control source of an UNBOUND control on the form:
=IIf(IsNull([txtdateofhire]),"Temp Employee",
DateDiff("yyyy",[txtdateofhire],[txtdateofinjury]))

NOTE: DateDiff will return the number of different years (not
necessarily full years) between the 2 fields, i.e. the result of
DateDiff("yyyy",#12/31/2004#,#1/1/2005#) is 1, even though the number
of days difference is also 1.

 

[Rick Brandt]

I can't get either of your things to work and I've asked this
question a few weeks ago and pretty much got the same answers and
they didn't work either. Could it be because I'm trying to enter
"TEMP EMPLOYEE" which is text into a box defined as Date?

Read Fred's response again. The expression goes into an UNBOUND control not the
control bound to the Date field. A control has no data type, only fields do.

As the control source of an UNBOUND control on the form:
=IIf(IsNull([txtdateofhire]),"Temp Employee",
DateDiff("yyyy",[txtdateofhire],[txtdateofinjury]))

NOTE: DateDiff will return the number of different years (not
necessarily full years) between the 2 fields, i.e. the result of
DateDiff("yyyy",#12/31/2004#,#1/1/2005#) is 1, even though the
number of days difference is also 1.