Same concept as an IF statement

[Guest]

I have a box calculated from two different dates to tell me how long an
employee has worked with us. We have temporary employees too. I would like my
box that display how many years an employee has worked here to display "TEMP
EMPLOYEE" if there is no Hire date. Thanks in Advance.

Travis

 

[Dirk Goldgar]

I have a box calculated from two different dates to tell me how long
an employee has worked with us. We have temporary employees too. I
would like my box that display how many years an employee has worked
here to display "TEMP EMPLOYEE" if there is no Hire date. Thanks in
Advance.

Travis

=IIf([IsNull([HireDate]), "TEMP EMPLOYEE", YearsBetween([HireDate],
Date()))


That assumes you call some function I've provisionally named
"YearsBetween" to calculate how many years there are between [HireDate]
and Date(). Note that DateDiff() alone won't do that.

 

[Guest]

I don't understand what you mean with the Years between thing. Can I just use
it or do I need to create some other function for it?

I have a box calculated from two different dates to tell me how long
an employee has worked with us. We have temporary employees too. I
would like my box that display how many years an employee has worked
here to display "TEMP EMPLOYEE" if there is no Hire date. Thanks in
Advance.

Travis

=IIf([IsNull([HireDate]), "TEMP EMPLOYEE", YearsBetween([HireDate],
Date()))


That assumes you call some function I've provisionally named
"YearsBetween" to calculate how many years there are between [HireDate]
and Date(). Note that DateDiff() alone won't do that.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[John Vinson]

I have a box calculated from two different dates to tell me how long an
employee has worked with us. We have temporary employees too. I would like my
box that display how many years an employee has worked here to display "TEMP
EMPLOYEE" if there is no Hire date. Thanks in Advance.

Travis

Set its control source to

IIF(IsNull([HireDate]), "TEMP EMPLOYEE", DateDiff(<your expression>))

John W. Vinson[MVP]

 

[Dirk Goldgar]

I don't understand what you mean with the Years between thing. Can I
just use it or do I need to create some other function for it?

I have a box calculated from two different dates to tell me how long
an employee has worked with us. We have temporary employees too. I
would like my box that display how many years an employee has worked
here to display "TEMP EMPLOYEE" if there is no Hire date. Thanks in
Advance.

Travis

=IIf([IsNull([HireDate]), "TEMP EMPLOYEE", YearsBetween([HireDate],
Date()))


That assumes you call some function I've provisionally named
"YearsBetween" to calculate how many years there are between
[HireDate] and Date(). Note that DateDiff() alone won't do that.

You'd have to write it. Here, take this "fncAge" function written by
Arvin Meyer and use it instead. Paste the code into a standard module.

'----- start of code -----
Function fncAge(DOB, Optional vDate)

' Author: Arvin Meyer, 5/15/97
' Notes: Age calculated as of vDate, or as of today if vDate is missing
' Optional parameter not supported in Access 1 or 2
' Arguments:
' DOB (Variant)
' vDate (Optional) (Variant)
' Returns:
' Age in years, for a person whose Date Of Birth is DOB

If Not IsDate(vDate) Then vDate = Date
If IsDate(DOB) Then
fncAge = DateDiff("yyyy", DOB, vDate) _
+ (DateSerial(Year(vDate), Month(DOB), Day(DOB)) > vDate)
Else
fncAge = Null
End If

End Function
'----- end of code -----

With that function defined in your database, set the controlsource of
the text box to:

=IIf([IsNull([HireDate]), "TEMP EMPLOYEE", fncAge([HireDate]))