Idea is Needed To Calculate an Annual Leave

[Habibi]

Hi Guys,

I'm creating a Project On VB6 which that keeps track of the Employees Information, Training Details, Salaries, Leave Details and the Payrol "Not Calculating the Payrol it's just Entering information". What I'm stuck in are:

1-: Since the employee has joined the organization, he / she should entitled an Annual Leave, Sick Leave. I want to caluclate the Leave that employee should have since date of Joining, substracting the days that has been taken from his Total Leave, as well as the Sick Leave.

the employee is entitled 21 days if he / she completed 4 years whilst 28 days if he / she completed 5 years.

Now, if I want to make all these calculations. Do I have to make the Formula within the Tables that has got the information? Or what is the idea to get rid of that ??

My Idea is:

This calculation is idepend on the Experaice for the employee. And I should calculate to get the Expreriance of the Employee and then Calculate how many days the emplyee entitled if he / she reaches the experiance rate.

Rs.Open "Data",Conn, .............,..............., adCmdTable
oD = DateDiff("YYYY", Now, Rs.Field("JoiningDate").Value)
oD = Rs.Field("Experiance).Value

If Rs.Field("Experiance).Value = 4 then 
RsLeave.Field("LeaveEntitled").value = 21
else
Rs.Field("LeaveEntitled).Value = 28

End IF
Rs.Close
RsLeave.Close

Is this idea is right ?? If not then what is the right thing to do. Please help as I'm a newby in the programming language.

many Thanks in advace,

habibalby

[RobDog888]

I think your logic is ok, but we can fix up your code a little to include an update
of the rs and handling if there are no records returned. Also, you have no
error handling either.

This is assuming that your Experience and JoiningDate fields are of a Date format and
LeaveEntitled is of a Numeric type format.

Rs.Open "Data",Conn, .............,..............., adCmdTable
'Check for records returned
If rs.bof = False and rs.eof = False Then
    oD = DateDiff("YYYY", Now, Rs.Field("JoiningDate").Value)
    If Rs.Field("Experiance).Value = 4 Then 
        rsleave.fields("LeaveEntitled").Value = 21
    Else
        rsleave.fields("LeaveEntitled").Value = 28
    End If
    rsleave.update
Else
    msgbox "Error - no records returned!"
End If
 
If rs.state = adstateopen Then Rs.Close
If rsleave.state = adstateopen Then RsLeave.Close
Set rs = Nothing
Set rsleave = Nothing

[Habibi]

It means I have to calculate the fields not the Value which is in the text Box that displayed from the Field itself ??

[RobDog888]

Could you explain more?

[Habibi]

Yes, What I mean to say. Is it possible calculating only the Objects rather than the DB Fields. I;E, In the First tab I have listed the all information for the employee inculding the Joining Date. Now, while the Joining Date is already assigned to an Object in the Form, is it possible to Calculate the Experiance from that Joining Date ? Not the Rs.Field("JoiningDate")

D = DateDiff("YYYY", Now, Me.txtJoiningDate.text)
txtExperiance.Text = D

What I want to know is once I calculated this Value, is it Possible to assign the Caluclated Field to Field in the Table ??

Or I have only one chooise is only Calculate the Fields ?? To assign the Value in to them ?

Many Thanks in advance,

Habibalby

[RobDog888]

Yes, you can calculate from the values in the textboxes instead of querying the db.
Use something like this format...

Forms![Form1]![txtLeave].Value

[Habibi]

Yes, you can calculate from the values in the textboxes instead of querying the db.
Use something like this format...

Forms![Form1]![txtLeave].Value

Yes, I understood that it can be. But the Main Question is will it be possible to enter a Calculated Field into a Database Field ??

Example:

Total = Val(Text1.Text) * Val(Text2.Text)
Text3.Text = Total

Now is it possible to enter the Total into a Database Field?

Thanks Mate and Happy New Year.

Habibalby

[RobDog888]

Yes, but you can only go one way for a calculated value. If your calculating it
from your ex. then you would have a field in the table that would take the
Total value. If you are trying to take a calculated value and place it into two
fields then you cant unless you have a formula to reverse engineer the values.

HTH

[Habibi]

Yes, but you can only go one way for a calculated value. If your calculating it
from your ex. then you would have a field in the table that would take the
Total value. If you are trying to take a calculated value and place it into two
fields then you cant unless you have a formula to reverse engineer the values.

HTH

From you experiance, what you choose if you were me. Calculating the field taken this method:

Rs.Open "Data",Conn, .............,..............., adCmdTable
'Check for records returned
If rs.bof = False and rs.eof = False Then
    oD = DateDiff("YYYY", Now, Rs.Field("JoiningDate").Value)
    If Rs.Field("Experiance).Value = 4 Then 
        rsleave.fields("LeaveEntitled").Value = 21
    Else
        rsleave.fields("LeaveEntitled").Value = 28
    End If
    rsleave.update
Else
    msgbox "Error - no records returned!"
End If

If rs.state = adstateopen Then Rs.Close
If rsleave.state = adstateopen Then RsLeave.Close
Set rs = Nothing
Set rsleave = Nothing

or doring this example:

Total = Val(Text1.Text) * Val(Text2.Text)
Text3.Text = Total

Thanks for your gratest reply dude.

Habibalby

[RobDog888]

Usually you dont save calculated fields like this because you need to calculate
it when its viewed anyways. You will always want the most current data and
if you retrieve a saved value for this, the value may be wrong if the entitled
leave changes everyday.

So, I guess I would not save the calculated field and just keep it on the form
as a calulated value from the two form fields.

[Habibi]

Usually you dont save calculated fields like this because you need to calculate
it when its viewed anyways. You will always want the most current data and
if you retrieve a saved value for this, the value may be wrong if the entitled
leave changes everyday.

So, I guess I would not save the calculated field and just keep it on the form
as a calulated value from the two form fields.

Yes, as per the Normalization Rules Calculated Fields must be put in a Seprate Table to save Database Size. But, in my case what about if I want to print a Report about the employee and I want to show the Experiance, Age, LeaveEntitled ect.. from the Calculated field ??

[RobDog888]

Then in your query for your report, you recalculate those calculated fields
just like you do on the form.

[Habibi]

Hummmmmmm, doubling the job in coding

[RobDog888]

If you want up to date data then re-calculating it is the way to go.

[Habibi]

If you want up to date data then re-calculating it is the way to go.

Can u have a look @ the Application and Advice for the best solution to be taken in this way ??
I want you to take a look on the Leave Form, the General Database tables ect....

Waiting ur reply please ?

Regards,
Habibalby