This access database produces the LOA letters that we send to employees telling them what their benefits are and what they are required to pay while they are out on leave. The report also displays anyone that has a benefit with a future dated coverage begin date.
Several of our employees have a 3 month waiting period before they receive their benefits. My dilema is this: I have employees that have multiple future dated coverage begin dates because for coverage like medical that's a 3 month waiting period versus life insurance which is a 6 month waiting period. Since there are two different dates the correct date doesn't always display in my letter to the employee. I've tried sorting the query different ways and that doesn'thelp. I know I need to filter it somehow. Here is the derived field I'm using in my query to show employees that have current coverage and it shows the coverage date for future dated rows too:
NEXTCHKDT: IIf(MAXCHKDT!COV_BEGIN_DT>Date(),MAXCHKDT!COV_BEGIN_DT,DateAdd('d',14,MAXCHKDT!MAX_CHECK_DT))
Several of our employees have a 3 month waiting period before they receive their benefits. My dilema is this: I have employees that have multiple future dated coverage begin dates because for coverage like medical that's a 3 month waiting period versus life insurance which is a 6 month waiting period. Since there are two different dates the correct date doesn't always display in my letter to the employee. I've tried sorting the query different ways and that doesn'thelp. I know I need to filter it somehow. Here is the derived field I'm using in my query to show employees that have current coverage and it shows the coverage date for future dated rows too:
NEXTCHKDT: IIf(MAXCHKDT!COV_BEGIN_DT>Date(),MAXCHKDT!COV_BEGIN_DT,DateAdd('d',14,MAXCHKDT!MAX_CHECK_DT))
