Same concept as an IF statement

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

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

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

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

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

Set its control source to

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

John W. Vinson[MVP]
 
Dahlman said:
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?

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

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

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]))
 
Back
Top