Adding time based on criteria.

[Guest]

I need to have a field that is set in the future based on criteria. I will
have a date an event occurred, and will have to add one, two or three years
depending on the level of the employee. Would this be an “If/Then†situation?

Thanks for the help and/or advice.

 

[Douglas J Steele]

Where are you trying to do this?

In a query, assuming there's a field EmployeeLevel that contains the values
1, 2 or 3, you could use something like

FutureDate: DateAdd("yyyy", [EmployeeLevel], [DateOccurred])

If you need to translate EmployeeLevel to 1, 2 or 3, you could use something
along the lines of:

FutureDate: DateAdd("yyyy", IIf([EmployeeLevel] < 5, 1,
IIf([EmployeeLevel] < 10, 2, 3)), [DateOccurred])

if, for instance, any employee with level less than 5 gets 1 year added,
anyone greater than 5 but less than 10 gets 2 and everyone else gets 3.

 

[Guest]

That is very helpful, thank you.

To further question:

If the Employee Level is a four digit number, the first two digits being the
pay grade (1 through 15) and the second two being the pay step (1 through
10), how can I have the querries key in on only the last two digits? Also,
the format will be steps 1 through 4, add 1 year, 5 through 7, add 2 years
and 8 through 10, add 3 years.

Where are you trying to do this?

In a query, assuming there's a field EmployeeLevel that contains the values
1, 2 or 3, you could use something like

FutureDate: DateAdd("yyyy", [EmployeeLevel], [DateOccurred])

If you need to translate EmployeeLevel to 1, 2 or 3, you could use something
along the lines of:

FutureDate: DateAdd("yyyy", IIf([EmployeeLevel] < 5, 1,
IIf([EmployeeLevel] < 10, 2, 3)), [DateOccurred])

if, for instance, any employee with level less than 5 gets 1 year added,
anyone greater than 5 but less than 10 gets 2 and everyone else gets 3.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I need to have a field that is set in the future based on criteria. I will
have a date an event occurred, and will have to add one, two or three years
depending on the level of the employee. Would this be an "If/Then" situation?

Thanks for the help and/or advice.

 

[Douglas J Steele]

Is Employee Level stored as a number, or as text? If text, try:

FutureDate: DateAdd("yyyy", IIf(CInt(Right([EmployeeLevel],2)) <= 4,
1,IIf(CInt(Right([EmployeeLevel],2)) <= 7, 2, 3)), [DateOccurred])

If it's a number, then

FutureDate: DateAdd("yyyy", IIf([EmployeeLevel] Mod 100) <= 4,
1,IIf([EmployeeLevel] Mod 100 <= 7, 2, 3)), [DateOccurred])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That is very helpful, thank you.

To further question:

If the Employee Level is a four digit number, the first two digits being the
pay grade (1 through 15) and the second two being the pay step (1 through
10), how can I have the querries key in on only the last two digits? Also,
the format will be steps 1 through 4, add 1 year, 5 through 7, add 2 years
and 8 through 10, add 3 years.

Where are you trying to do this?

In a query, assuming there's a field EmployeeLevel that contains the values
1, 2 or 3, you could use something like

FutureDate: DateAdd("yyyy", [EmployeeLevel], [DateOccurred])

If you need to translate EmployeeLevel to 1, 2 or 3, you could use something
along the lines of:

FutureDate: DateAdd("yyyy", IIf([EmployeeLevel] < 5, 1,
IIf([EmployeeLevel] < 10, 2, 3)), [DateOccurred])

if, for instance, any employee with level less than 5 gets 1 year added,
anyone greater than 5 but less than 10 gets 2 and everyone else gets 3.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I need to have a field that is set in the future based on criteria. I will
have a date an event occurred, and will have to add one, two or three years
depending on the level of the employee. Would this be an "If/Then" situation?

Thanks for the help and/or advice.