Conditional value without circular function

[SC in Texas]

I am trying to create a formula in a textbox that will only add a biweekly
earnings to sick hours when the total sickhours are below a cap of 1600 hrs.

The current set up in my report has a txtBeginSickHrs ( Beginning hours for
the calendar year) in the header of the report.
I then have txtSickUsed (a running sum of sick hour used) and txtSickEarned
(a running sum of the pay period earning rate of sickleave)
txtSickBal is a textbox that dispays the new balance for each payperiod where
txtSickBal = txtBeginSickHrs + txtSickEarned - txtSickUsed

My problem is that txtSickEarned should be zero whenever the previous
txtSickBal >= 1600 and should be the normal rate anytime txtSickBal < 1600

The only ways I have come up with for controlling the txtSickEarned toggle
between zero and normal hours create a circular function, therefore an error.

Any help would be appreciated.
Thanks.

 

[Clifford Bass]

Hi,

I do not see anywhere in there where the biweekly earnings value comes
from. How are you setting the txtSickEarned value? From where does the
value originate?

Clifford Bass

 

[SC in Texas]

it is a constant value that I added in the IIF statement when the balance was
< 1600 or 0 when it was >= 1600 hrs. One of the kickers is that this rule on
the cap of sick leave is realative new and people who had over 1600 hrs kept
what they had until they use enough to drop below the 1600. anyone at or
above 1600 do not earn any additional hours until they drop below 1600.
Therefor I can not just test for a new total of 1600 and make it an arbritary
1600 if the formula puts it above 1600.

 

[SC in Texas]

I know if I was doing this by hand I could look at the previous balance and
decide weither the 4 hrs sick time would be added or if 0 hrs would be added.
My problem is how do I get the computer to look at the previos value of a
field or text box. I guess I was trying to do this within the report with out
having to write any visual basic functions to do so.

I have also looked at trying to do this in a query but keep running into the
same trap of how do I look at the previous value of the balance for deciding
what to do on this record.
Thanks,
SC

 

[Clifford Bass]

Hi SC,

Instead of constants placed into the IIf() function I would suggest
that you use a settings table to store them. That way next year when the
economy has improved (hopefully) and the company wants to provide more
benefits, all that needs to be done is change a value in the table instead of
having to modify the query or code somewhere.

To do that create a table "tblSettings" with three fields. One named
"RecordID", an integer field with the default set to 1 and the validation
rule set to one. Make it the primary key. The second and third named
"BiweeklySickLeaveAllotment" and "MaximumSickLeaveHours", both integers. Add
one row to your table with the appropriate values. Add two invisible fields
to your report header with the following control sources:

txtBiweeklySickLeaveAllotment
DLookup("BiweeklySickLeaveAllotment", "tblSettings")

txtMaximumSickLeaveHours
DLookup("MaximumSickLeaveHours", "tblSettings")

Then you can do change your IIf() function to a Switch() function:

txtSickEarned = Switch(txtBeginSickHrs - txtSickUsed >
txtMaximumSickLeaveHours, 0, txtBeginSickHrs + txtBiweeklySickLeaveAllotment
- txtSickUsed > txtMaximumSickLeaveHours, txtMaximumSickLeaveHours -
txtBeginSickHrs, True, txtBiweeklySickLeaveAllotment)

So, this pre-calculates the sick balance within itself rather than
using the final value, thereby eliminating the circular reference. Sick
hours earned will be 0 when the begin sick hours, less the sick hours used is
above the maximum (1600). Sick hours earned will be enough to get the
employee up to the maximum, but not above if the employee would go over.
Otherwise it is the standard allotment.

Hope that helps,

Clifford Bass

 

[SC in Texas]

Clifford,
Thanks for the help. I am going to have to study through this to see if it
helps.

It would have been a big help if they had not grandfathered in the peoples
hours that already had a balance of greater than 1600 hours. I could just set
max at 1600 and work with that.

Thanks,
Steve

 

[Clifford Bass]

Hi Steve,

You are welcome. If you have questions, or it does not work, post back.

Not grandfathering in those with more sick leave might cause a
revolt--far easier to make you deal with it, which equates to job security of
a sorts

Clifford Bass

 

[SC in Texas]

Right now this is not working but I am still working with it. Maybe I will
get it figured out.
One of my problems is that I don't want to spend a lot of time with it
because this system is being replaced with a new one in a couple of months.
Thanks.
SC

 

[SC in Texas]

I think I need to clarify what my report is.
It is a calendar of hours worked/earned with a payperiod by payperiod
balance of the vacation, sickleave and comp time available at the end of each
pay period.
Your formulae and mine both work well on the first payperiod or two but
after the max has been reached then I have not figured out a way of holding
the sick leave earned total until the balance drops below the max again.
Currently if the balance is below max but the weekly rate of 4.6 hours would
put it over they add the complete 4.6 hours then stop the hours being added
until the balance is below the 1600 max again.

I don't have very many employee's crowding or over that max but would like
to make this report work as well for them as it does for the newer employees.

I think I have all of the nightmares worked out on the vacation balances but
this sick hours balance is proven difficult.

Thanks for the help.
Steve

 

[Clifford Bass]

Hi Steve,

I think I follow. But just to be sure, would you provide a sample of
several rows of the data coming into the report and what the desired output
would look like?

Thanks,

Clifford Bass

 

[SC in Texas]

The report has a report header showing the personal info for the employee,
name, employee number, employe start date, years beginning vacation balance,
beginning sick leave balance and beginning comp time balance
Page header has titles
Date Sat Sun Mon Tue Wed Thur Fri Sat Sun Mon Tue Wed Thur Fri Date
Begin
End
then there is a group header for payperiod that shows the dates of the each
day in the pay period across the header. example of header:
12/20/08 20 21 22 23 24 25 26 27 28 29 30 31 1
2 1/2/2009

Then the detail lines shows each days hours and codes if the they are
something special.
8V 8V 8V 8H 8H 8v 8
8 8H 8
These actually use different rows for each code
Then the pay period footer (where my problem is)

Balance: Vacation 86 Sick 1600.68 Comp Time 0

With my formula the first three values for sick are 1600.8, 1600.8 and 1597.3.
because of 8S on the third payperiod.

The problem is that I have found no way of counting the number of times the
4.62 hrs has been added with out creating a circular function.

Ignoring the max hours problem, my previous version of this calendar uses a
running sum text box for adding the 4.62 each pay period example of previous
formula:
SickBal = BegSickBal - [sickused]+[sickearned] where sick used is a running
sum text box of sick hours used each payperiod and sickearned is a running
sum text box of =4.62.

Starting and stopping the = 4.62 is dependent on the sickbal of the previous
payperiod.

Is there a way with visual basic to capture the value from the previous
group footer and then using that in the current group footer.

I think I could run an analyze in excel and then correct the formula there
but who wants to do that with every report for each employee who is crowding
1600 hrs.

Thanks for all of your help so far. I hope this helps you see what I am
trying to do with out confusing you to much. I have a similar problem in a
subreport that is at the top of the payperiod time sheet they print each pay
period to turn in to the pay clerk. The subreport is there only for the
foremans use and I have threatened to remove it since I cannot get it to work
correctly. I was trying to handle these calculations in the source query but
have the same problem of not being able to control when the 4.62 is added.

Thanks
Steve

 

[Clifford Bass]

Hi Steve,

It should be doable. I will let it percolate in my mind a bit--there
are some ideas niggling in the back there.

Meanwhile, some further questions: How are the hours entered for a pay
period for a particular person? All in one record or in multiple records?
If all in one record, do you have total fields for the different types of
hours--in particular for the sick leave? Are the hours entered separately
from the codes (I hope)? Are the pay periods always two weeks? If so, it
will greatly simplify things. If not, do you store, besides the begin date,
the end date or the length?

Clifford Bass

 

[SC in Texas]

The hours are entered in by daily work orders. Certain activity codes denote
what type of work or leave the hours represent. One catch is that the
activity codes are in the work order table and not in the labor cost table
where the hours are, not a major problem just requires a join query. There
for they are in multiple records.
I have conditional totals for different types of paycodes which are tied to
the activity codes.

Pay periods are always two weeks. The begin dates and end dates are not
stored but calculated in the form that I kick this report off from.

I have contol buttons that are used to move from pay period to pay period.
In the form I have a control for the start date, a control for the end date,
a control for the previous start date, and one for the next start date.

The calendar uses the end date control to select the year and always runs
from the first payperiod to the last that has any data in the system for it.
In other words any ending date in 2008 will bring up the complete 2008
calendar. Any ending date in 2009 brings up all data from first payperiod in
2009 to current pay period info.
Note: the first pay period for 2009 is 12/20/2008 to 1/2/2009. (These
creates it own mess with vacation, which is suppose to run from 1/1 to 12/31
but if after 12/19 gets charged to this year. but that is a whole different
story)

I would say for me this is one of those problems that nags and nags and
won't go away until I find a solution. If we can do it so easy on paper we
should be able to make the computer do it. So far all of my proposed
solutions have the same problem of counting when the sick hours earned is
added and not counting them when they are not with out creating a circular
function trying to use the count.
Other options of knowing when to add them or not add them with out knowing
the count to figure what to add to the beginning sick hours for new current
balances.

Thanks for the time and effort. I hope one of us makes a break through in
this. If I find a solution I will be glad to share it with you.

Thanks,
Steve

 

[Clifford Bass]

Hi Steve,

As I think on this, I lean more and more towards doing the computations
and storing them in a employee-payperiod summary table. Then all you have to
do is link to that table. There is a time to calculate on the fly and there
is a time to store the calculations. In my opinion, this is the latter
because of the changing nature of the constraints. Suppose you are asked to
go back and run a report for last year and you have the report set up for
this year? Or suppose they change the conditions again in six months? You
would then have a report that has to figure out which values to use depending
on which part of the year you are in. Depending on your situation, it may
make sense to do the computations and saving when the hours are entered or as
a separate batch process that adds in new rows for the new pay period. Going
back to the settings table I mentioned earlier, here is how I would adapt it
to fit this scenario. Instead of a single row, allow for multiple rows.
Replace the RecordID with an EffectiveDate field, which will be the primary
key. You could also add in columns for your other types of leave such as
vacation. Each time one or more of the values changes, add a new row with a
new effective date, copying the old data that does not change and entering
the new changed data. Backing up a bit, your employee payperiod summary
table could contain things like earned sick leave, used sick leave, earned
and used vacation, earned and used comp. time, and so forth, along with sick
leave balance, vacation balance, comp. time balance, etc. The primary key to
the table might be the combination of the employee ID and the starting date
of the pay period. Jumping forward, when doing the computations of the
summary information, you would check the settings table for the most recent,
but not future, row of information as compared to the pay period start and/or
end date and use the conditions specifice there.

So if the settings table has these values:

EffectiveDate MaxSickHours BiweeklySickAllotment
1/1/2006 5000 6.23
1/1/2009 1600 4.62
1/1/2010 1800 5.00

and you are calculating for the pay period that ends on June 5, 2009 you
would use the 1/1/2009 row. You can get the row with:

select *
from tblSettings as A
where EffectiveDate <= [the pay period end date] and not exists
(select * from tblSettings as B
where B.EffectiveDate > A.EffectiveDate)

The vacation issue you have alluded to most likely would be solved by
doing things this way.

You state that the system is to be replaced soon and you do not want to
spend a lot of time. I don't think it would take much time to put this in
place. You could start by pre-populating the table with a summary record for
the last pay period in 2008, then only create new records for 2009.

If you do not want to go that route, here is an untested alternative:
In your report's code, after the Option statements, before any
subroutines/functions add:

Private m_sngSickBalance As Single

Create an On Format event for the header where you display the employee
information (ID, name, starting sick leave balance, etc.). In it place:

m_sngSickBalance = txtBeginSickHrs

Create an On Format event for the detail. In it place:

m_sngSickBalance = m_sngSickBalance - txtSickUsed
If m_sngSickBalance < 1600 Then
m_sngSickBalance = m_sngSickBalance + 4.62
End If

Create an On Format Event for the employee's footer section. In it
place:

txtSickBalance = m_sngSickBalance

Hope that does the trick.

Clifford Bass