Question Months Between Two Dates

[Nowsherwan]

Hy All!
I am new to access i would like to mantain an emloyees earned leave record..
The Scenrio is: Every employee credit two earned leave for each month from the date of joining to the current date, two leave will be credited exactly at the Month end, but if the joining date is greater then the 15th of that month then no leave will be credited for the specific month...

How do i calculate the months between two dates i.e Joining date & current date..?
How do i credit the two leave to every employee after the each month..?
How i restrict the system to can't credit the leave if the joining date is greater then the 15th of that month..?

Despretely witing to your's replay...
Thanks in Advance

 

[DCrake]

Simple Software Solutions

Try looking at the DateDiff() function

Eg
Interval = DateDiff("m",Start,End)

 

[raskew]

How do i calculate the months between two dates i.e Joining date & current date..?

Use the DateDiff() function. Example:
x = #3/21/06#
? datediff("m", x, date())
27

How i restrict the system to can't credit the leave if the joining date is greater then the 15th of that month..?

Add the following boolean statement to the datediff() calculation.
+ (day(x)>=15)
It'll return -1 if true, 0 if false. Example:
x = #3/21/06#
? datediff("m", x, date()) + (day(x)>=15)
26

HTH - Bob

 

[Nowsherwan]

Use the DateDiff() function. Example:
x = #3/21/06#
? datediff("m", x, date())
27



Add the following boolean statement to the datediff() calculation.
+ (day(x)>=15)
It'll return -1 if true, 0 if false. Example:
x = #3/21/06#
? datediff("m", x, date()) + (day(x)>=15)
26

HTH - Bob

Thanks

*But if the duty period after joining date is equal to or greater then 15 in a month is treated as full calender month and we will credit two leaves... how i calulate 15 days after joining date if months is 31 days or 30 days...

waiting to your replay

 

[wazawak]

What about calculating weeks? Would I simply replace the "m" with a "w," or is there additional revising I'd have to do?

 

[raskew]

... how i calulate 15 days after joining date if months is 31 days or 30 days...

x = #3/16/08#
? day(DateSerial(year(x), month(x) + 1, 0))-day(x)+1
16

DateSerial(year(x), month(x) + 1, 0) returns the last day of the month, regardless of the month's length.

I'm assuming you'd count the start date as day 1, thus the +1

What about calculating weeks? Would I simply replace the "m" with a "w,"

Yes, but keep in mind that anywhere from 7 to 13 days will return as 1 week.

HTH - Bob

 

[Nowsherwan]

Update Pervious Value With New Value

Hy!

I would like to update value with new value for the same record Suppose:



E_ID-------Balance-------- Debit---------- NewBalance
101 ----------20 -----------------5 ------------------15
---------------15 -----------------5 ------------------10
---------------10 ---------------- 2 -------------------8
102--------- 20 ---------------- 10 ----------------- 10
---------------10 ----------------- 2 -------------------8

How do i update the value of balance from new balance for the same employee where E_ID is primary key....

Waiting to your replay
Thanks in Advance

 

[neileg]

It is a better design to calculate the balance when you need it and not to store the balance. Obviously yout need to know the entitlement and then subtract any leave taken or booked.