IF and DateDiff

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
Click to expand...

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.
 
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?

fredg said:
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
Click to expand...

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.
Click to expand...
 
Dahlman said:
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?
Click to expand...


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.

fredg said:
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.
Click to expand...
Click to expand...
 
Back
Top