Calculating seniority

[Céline Brien]

Hi everybody !

Employees accumulating seniority : 200 days = 1 year

If an employees has accumulated 278 days, I would like to show it as 1 year
78 days.

The following formula would do the job :

=IFF([Seniority]>200;"1 year " & ([Seniority]-200);[Seniority])

But what for employees with 12 years of seniority ? It would need to many
IFF

In Excel I would use this formula :

=ENT([Seniority]/200)&"year "&MOD([Seniority];200)

For 678

ENT([Seniority]/200) = 3

MOD([Seniority];200) = 78

How would you do it in Access ???

Many thanks for your help,

Céline

 

[Douglas J. Steele]

Take advantage of \, which is Integer Division.

[Seniority] \ 200 & " year, " & [Seniority] Mod 200

 

[Céline Brien]

Hi Douglas,
It is working perfectly !
I might just add a IFF to obtain years with an S for 2 years and over.
Thank you very much for your help.
Céline

Take advantage of \, which is Integer Division.

[Seniority] \ 200 & " year, " & [Seniority] Mod 200

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi everybody !

Employees accumulating seniority : 200 days = 1 year

If an employees has accumulated 278 days, I would like to show it as 1
year 78 days.

The following formula would do the job :

=IFF([Seniority]>200;"1 year " & ([Seniority]-200);[Seniority])

But what for employees with 12 years of seniority ? It would need to many
IFF

In Excel I would use this formula :

=ENT([Seniority]/200)&"year "&MOD([Seniority];200)

For 678

ENT([Seniority]/200) = 3

MOD([Seniority];200) = 78

How would you do it in Access ???

Many thanks for your help,

Céline

 

[Duane Hookom]

Couple comments:
1) The function is IIf() not IFF() so you don't get syntax errors.
2) This is a business rules type calculation that I would create a
user-defined function for. You can then use the function anywhere and WHEN
the business rule changes, you can find and change it in one place. Using
Doug's expression:

Public Function GetSeniorty(intDays as Integer) as String
GetSeniority = intDays \ 200 & " year, " & intDays Mod 200
End Function

You can then use the GetSeniority() function almost everywhere in code,
control sources, and queries.
--
Duane Hookom
MS Access MVP

Hi Douglas,
It is working perfectly !
I might just add a IFF to obtain years with an S for 2 years and over.
Thank you very much for your help.
Céline

Take advantage of \, which is Integer Division.

[Seniority] \ 200 & " year, " & [Seniority] Mod 200

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi everybody !

Employees accumulating seniority : 200 days = 1 year

If an employees has accumulated 278 days, I would like to show it as 1
year 78 days.

The following formula would do the job :

=IFF([Seniority]>200;"1 year " & ([Seniority]-200);[Seniority])

But what for employees with 12 years of seniority ? It would need to
many IFF

In Excel I would use this formula :

=ENT([Seniority]/200)&"year "&MOD([Seniority];200)

For 678

ENT([Seniority]/200) = 3

MOD([Seniority];200) = 78

How would you do it in Access ???

Many thanks for your help,

Céline

 

[Douglas J. Steele]

Or to provide Céline's requested addition:

Public Function GetSeniorty(SeniorityDays as Integer) as String
Dim intDay As Integer
Dim intYear As Integer

intYears = SeniorityDays \ 200
intDays = SeniorityDays Mod 200

Select Case intYear
Case 0
GetSeniority = vbNullString
Case 1
GetSeniority = intDays \ 200 & " year, "
Case Else
GetSeniority = intDays \ 200 & " years, "
End Select

Select Case intDay
Case 1
GetSeniority = GetSeniority & intDay & " day"
Case Else
GetSeniority = GetSeniority & intDay & " days"
End Select

End Function


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Couple comments:
1) The function is IIf() not IFF() so you don't get syntax errors.
2) This is a business rules type calculation that I would create a
user-defined function for. You can then use the function anywhere and WHEN
the business rule changes, you can find and change it in one place. Using
Doug's expression:

Public Function GetSeniorty(intDays as Integer) as String
GetSeniority = intDays \ 200 & " year, " & intDays Mod 200
End Function

You can then use the GetSeniority() function almost everywhere in code,
control sources, and queries.
--
Duane Hookom
MS Access MVP

Hi Douglas,
It is working perfectly !
I might just add a IFF to obtain years with an S for 2 years and over.
Thank you very much for your help.
Céline

Take advantage of \, which is Integer Division.

[Seniority] \ 200 & " year, " & [Seniority] Mod 200

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi everybody !

Employees accumulating seniority : 200 days = 1 year

If an employees has accumulated 278 days, I would like to show it as 1
year 78 days.

The following formula would do the job :

=IFF([Seniority]>200;"1 year " & ([Seniority]-200);[Seniority])

But what for employees with 12 years of seniority ? It would need to
many IFF

In Excel I would use this formula :

=ENT([Seniority]/200)&"year "&MOD([Seniority];200)

For 678

ENT([Seniority]/200) = 3

MOD([Seniority];200) = 78

How would you do it in Access ???

Many thanks for your help,

Céline