Query help

[Tara]

I'm trying to create a query that will calculate how much someone has
contributed year-to-date to their supplemental health care plan. Basically
the employee agrees to contribute an amount of their choosing. This amount
is then divided out so that the individual pays a certain portion of that out
of each bi-weekly paycheck. The part that is causing me headaches is that we
want to stop calculating a running total for those employees who terminate,
and instead calculate their contributions only up to their termination date.
Here's what I have so far:

This is what I had originally and it works great to calculate the YTD
amount, but doesn't take into account terminated employees:

ytd:
IIf(Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26)>[tbl125]![curContrib],[tbl125]![curContrib],Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26))

Then, I tried to modify that so that terminations were taken into account.
It keeps giving me error messages (and with good reason I'm sure, since I
have a feeling I'm approaching this all wrong). Here is that version:

ytd: IIf([TerminationDate] Is
Null,IIf(Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26)>[tbl125]![curContrib],[tbl125]![curContrib],Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26)),
IIf(Int(([TerminationDate]-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26))


Any help is appreciated!

 

[Jerry Whittle]

Your last IIf statement is missing arguments. Also it looks like the first
has too many arguments.

But before going there consider: some years have more than 26 bi-weekly
paychecks! It happens about once every 7 or 8 years. That would make a big
mess of your calculations. Question: what was the date of your most recent
payday? Given that, I could figure out when you have years with 27 paydays.

 

[Tara]

I hadn't even thought of that! How would I calculate that without setting a
constant divisor?

Our last paycheck was on 11/23/2007

Thanks for your help!

Your last IIf statement is missing arguments. Also it looks like the first
has too many arguments.

But before going there consider: some years have more than 26 bi-weekly
paychecks! It happens about once every 7 or 8 years. That would make a big
mess of your calculations. Question: what was the date of your most recent
payday? Given that, I could figure out when you have years with 27 paydays.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm trying to create a query that will calculate how much someone has
contributed year-to-date to their supplemental health care plan. Basically
the employee agrees to contribute an amount of their choosing. This amount
is then divided out so that the individual pays a certain portion of that out
of each bi-weekly paycheck. The part that is causing me headaches is that we
want to stop calculating a running total for those employees who terminate,
and instead calculate their contributions only up to their termination date.
Here's what I have so far:

This is what I had originally and it works great to calculate the YTD
amount, but doesn't take into account terminated employees:

ytd:
IIf(Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26)>[tbl125]![curContrib],[tbl125]![curContrib],Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26))

Then, I tried to modify that so that terminations were taken into account.
It keeps giving me error messages (and with good reason I'm sure, since I
have a feeling I'm approaching this all wrong). Here is that version:

ytd: IIf([TerminationDate] Is
Null,IIf(Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26)>[tbl125]![curContrib],[tbl125]![curContrib],Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26)),
IIf(Int(([TerminationDate]-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26))


Any help is appreciated!

 

[Jerry Whittle]

If your paydays are on Wednesdays, there will be 27 of them in 2008.

Debug.Print CInt(DatePart("ww",#12/31/2008#,4)/2) = 27

Come to think about it, 27 paydays might be more common than that. For
example if payday was on January 1, 2007, the employees would also be due a
paycheck on December 31st, 2007 which would be their 27th paycheck of the
year.

Then you may also need to factor in business rules such as what happens when
payday is on a holiday like January 1, 2007.

Hardcoding in the 26 paydays is probably a mistake. I think this is why many
companies 2 paydays a month which is always 24 paydays to avoid this problem!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Your last IIf statement is missing arguments. Also it looks like the first
has too many arguments.

But before going there consider: some years have more than 26 bi-weekly
paychecks! It happens about once every 7 or 8 years. That would make a big
mess of your calculations. Question: what was the date of your most recent
payday? Given that, I could figure out when you have years with 27 paydays.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm trying to create a query that will calculate how much someone has
contributed year-to-date to their supplemental health care plan. Basically
the employee agrees to contribute an amount of their choosing. This amount
is then divided out so that the individual pays a certain portion of that out
of each bi-weekly paycheck. The part that is causing me headaches is that we
want to stop calculating a running total for those employees who terminate,
and instead calculate their contributions only up to their termination date.
Here's what I have so far:

This is what I had originally and it works great to calculate the YTD
amount, but doesn't take into account terminated employees:

ytd:
IIf(Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26)>[tbl125]![curContrib],[tbl125]![curContrib],Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26))

Then, I tried to modify that so that terminations were taken into account.
It keeps giving me error messages (and with good reason I'm sure, since I
have a feeling I'm approaching this all wrong). Here is that version:

ytd: IIf([TerminationDate] Is
Null,IIf(Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26)>[tbl125]![curContrib],[tbl125]![curContrib],Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26)),
IIf(Int(([TerminationDate]-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26))


Any help is appreciated!

 

[Tara]

Our paydays are on Fridays. Not that it matters really. There will still be
years when we would get 27 pays. Also, our checks are direct deposited, so
I'm not sure that holidays and bank closures really matter as it's all done
electronically.

I just looked at my expression again and realized something. In my nested
IIf Statement, I accounted for the possibility of overpayment by making sure
that the YTD balance was no more than the total yearly contribution amount.
So, if I could get that statement to work, I should be covered. All it would
mean is that in years with 27 pays, employees would get one paycheck that had
no health plan deductions out of it.

The message it kept giving me was about too few/too many arguments, but try
as I might, I couldn't see where the issue was!

If your paydays are on Wednesdays, there will be 27 of them in 2008.

Debug.Print CInt(DatePart("ww",#12/31/2008#,4)/2) = 27

Come to think about it, 27 paydays might be more common than that. For
example if payday was on January 1, 2007, the employees would also be due a
paycheck on December 31st, 2007 which would be their 27th paycheck of the
year.

Then you may also need to factor in business rules such as what happens when
payday is on a holiday like January 1, 2007.

Hardcoding in the 26 paydays is probably a mistake. I think this is why many
companies 2 paydays a month which is always 24 paydays to avoid this problem!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Your last IIf statement is missing arguments. Also it looks like the first
has too many arguments.

But before going there consider: some years have more than 26 bi-weekly
paychecks! It happens about once every 7 or 8 years. That would make a big
mess of your calculations. Question: what was the date of your most recent
payday? Given that, I could figure out when you have years with 27 paydays.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm trying to create a query that will calculate how much someone has
contributed year-to-date to their supplemental health care plan. Basically
the employee agrees to contribute an amount of their choosing. This amount
is then divided out so that the individual pays a certain portion of that out
of each bi-weekly paycheck. The part that is causing me headaches is that we
want to stop calculating a running total for those employees who terminate,
and instead calculate their contributions only up to their termination date.
Here's what I have so far:

This is what I had originally and it works great to calculate the YTD
amount, but doesn't take into account terminated employees:

ytd:
IIf(Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26)>[tbl125]![curContrib],[tbl125]![curContrib],Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26))

Then, I tried to modify that so that terminations were taken into account.
It keeps giving me error messages (and with good reason I'm sure, since I
have a feeling I'm approaching this all wrong). Here is that version:

ytd: IIf([TerminationDate] Is
Null,IIf(Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26)>[tbl125]![curContrib],[tbl125]![curContrib],Int((Date()-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26)),
IIf(Int(([TerminationDate]-[tbl125]![dtmPlanStart])/14)*([tbl125]![curContrib]/26))


Any help is appreciated!