Expression Field

[Maleficent]

I need to work out sick pay entitlement which is done based on length of service

I have a field called "Start Date"

I want to add a field called "Sick Pay Entitlement" that will auto-populate subject to "Start Date"

eg. 2 years or less = 6 Weeks
2-5 years = 13 Weeks
5 years + = 26 Weeks

What is the best way to do this?

Many thanks

 

[Minty]

As this value changes you would simply calculate for display where needed.
Something like

Entitlement: IIf(DateDiff("yyyy",[StartDate],Date())<=2,6,IIf(DateDiff("yyyy",[StartDate],Date())>5,26,13))

I think the way datediff years calculates is a bit weird from memory, so you may be better to use months for the comparison.

 

[Maleficent]

Thanks Minty - that makes sense but i'm getting a #Func! result!

 

[Maleficent]

PS. I changed it to months using "mm"

 

[plog]

I think the way datediff years calculates is a bit weird from memory,

All the date intervals are like that. DateDiff takes just that interval from each date and does math:

1/1/2018 - 12/31/2017 = 2018 - 2017 = 1 Year
11/1/2017 - 10/31/2017 = 11 - 10 = 1 Month


So it would probably be best to use the smallest tolerable interval and just do math yourself to arrive at correct value.

 

[Minty]

PS. I changed it to months using "mm"

Months is "m"

As plog stated the maths can be a little misleading.

 

[Mark_]

Minty's solution is written as an expression in a query, so it may or may not work for a given situation.

Alternate to Minty would be to add a table you can look these values up from then do so based on their length of service. Normally this would be joined in during a query, but would also allow you to use domain functions to do a DLookup from a form.

Both will get you the same results, but it mostly depends on how often you need to do this, if this is a value that changes, and where you plan to do this.