Due Vacation Calcaulations!

[cap.zadi]

I have a employee database.



i want to make a query which can let me know about the next dua vacation of Employee. some of have after 6 months, some have after 1 year and some have after 2 years.

so please advise how to calculate it from Hiringdate.

thanks

zee

[apr pillai]

I think the best approach is to maintain the structure of the employee record with the following fields:

Hiring Date, Resumption Date, Freqency days, Due Date
01-01-2011 01-01-2011......... 180 ...............30-06-2011

The employee goes on vacation on 30-06-2011 and comes back, say after 15 days, let the resumption date updated to 16-07-2011. Let the initial hiring date remain as it is for record.

You can calculate the Due Date column in the Query with the expression: [Resumption Date]+[Frequency Days]. The frequency in days is easier for calculations.

[June7]

You will need a field in table that identifies the period. All values in same unit (6, 12, 24 months) will make it easier. Then use DateAdd function. See http://msdn.microsoft.com/en-us/libr...(v=vs.85).aspx

[cap.zadi]

Dears Thanks for the solution and it is working good.

can i ask you teh question?

how i can calculate the experience of the emp within the company like

JoiningDate
01/03/2009

so what will be his exp within the company in years/months/days so far?

thanks

zee

[June7]

Check this http://bytes.com/topic/access/answer...at-years-month

[cap.zadi]

Sorry Sir,

it is open to webpage but no text is there. seems article is remove or blocked.

thanks

[June7]

This is the code it offered:
Dim Date1 as Date
Dim Date2 as Date
Dim Years as Integer
Dim Months as Integer
Date1=Cdate(txtDate1)
Date2=Cdate(txtDate2)
Years=DateDiff("YYYY",Date1,Date2)
Months=DateDiff("M",Date1,Date2) - (Years*12)
txtYIS=Format(Years) & " yrs " & format(Months) & " mth"

Or try this one http://groups.google.com/group/micro...03152fa335dbe9

Google:
vba calculate service time
vba calculate years in service