Using MS Access/MS Access

Advertisement


Question
Hi,
I'm trying to calculate length of employment in days from an employee table. Some employees are still working so I'm not sure how to write the condition for this using a query.I have an EndDate column & a StartDate column.I also created a column Now(). I would really appreciate if you can please help me.

Answer
Hi Reenu,

Let's assume you have 3 fields

[Start Date]
[End Date]
[Employment Days]

Where [Employment Days] = [Start Date]until today's date.

You need to use a DateDiff() function.
Please check this link for the entire usage: http://office.microsoft.com/en-in/access-help/datediff-function-HA001228811.aspx

YouTube Video: http://www.youtube.com/watch?v=hOPxCcXzAjo


DateDiff(interval, date1, date2 [, firstdayofweek] [, firstweekofyear] )

In your case
DateDiff(interval, [Start Date], Now() [, firstdayofweek] [, firstweekofyear] )

The above should be the code on the AfterUpdate event of the [Start Date] field. So no matter however long any employee has worked or is still working you will get the exact count of employment days until date.

Hope this will help

Best regards
Manish Batola  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Manish Batola

Expertise

ALL MICROSOFT ACCESS DESIGN AND Development QUESTIONS which occur in a Access Projects Lifecycle. Can offer constructive help on Designing Advanced MS-ACCESS Applications such CRM/FRM(Follow-up),HSE-Health,Safety & Environmental Applications,School Management Systems,Hotel Software, Hospital Software,Chemicals Indenting,Import/Export,MRP - Manufacturing Resource Planning, ERP - Enterprise Resource Planning, Automotive Management Systems, Business Intelligence for Oil & Gas Drilling Operations, CRM,KPI- Key Performance Indicators,HealthCare, Hospitality, Constructions, Loyalty Clubs, Call Centers, Travel & Tourism, Educational Institutions, Industrial Strength Invoicing and many more being added every year.

Experience

29+ yrs in the Software industry with 23yrs completely dedicated on more than 350 Various Microsoft Access Projects from different Industry Sector[s]. I'm using Access ever since it was born.

Organizations
Worked in various multinationals catering from Aerospace, Oil & Gas etc.

Education/Credentials
-BSc - Computer Science -MSAP (Master in Software & Applications Programming) -BPRE (Business Process Re-engineering Engineer) -SQAE (Software Quality Assurance Engineer) -STIE (Software Testing & Inspection Engineer) -Project Engineer (Software Development)

Past/Present Clients
Catered to various clients in the following industries • Oil & Natural Gas (BPM/CRM/Access Development) • Sales / Marketing (CRM) • Automotive (Automobile Dealers) • Logistics & Indenters (Import/Export) • HealthCare / Hospitals • Hotels 3* & 5* • Restaurants • Construction Co’s (Project Billing) • Clubs (Concept Selling) • Shoe-Chains • Solicitors/Lawyers • Call Centers • Aerospace (Preventive Maintenance) • Travel & Tourism • Computer Vendors • Manufacturing • Music Shops • Education • Data Warehousing • Health,Safety & Environmental Applications • Mud Engineering • School Management Software

©2016 About.com. All rights reserved.