Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 26th, 2005, 05:44 PM
Registered User
 
Join Date: Nov 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using DateDiff

Hi

I have an Employee database which has three tables. I have the employee table for employee details, a holiday table and a sickness table. I'veset up the relationships etc and a for which displays info about an employee. I can insert (through the form) a start and end date of a holiday or a sickness/absence and I want to calculate the number of working days the employee has had off each time. For example, an emplyee has 20 days holiday entitlement, and if an Employee books a holiday I want the form to deduct the total number of working days from the holiday entitlement. How do I implement this within the form? If I have a DateDiff function in a module, how do I get the form to use this function?

I'm a newbie to access, please help!

Thanks

Ian:)

 
Old November 27th, 2005, 01:03 AM
Authorized User
 
Join Date: Nov 2003
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to bjackman Send a message via MSN to bjackman
Default

add a field to each of the 2 subtables to hold the value of how many days they have taken off. You can then use an after update event on the end date and have it use the datediff function. the syntax would be similiar to this, substitute your field names where appropriate.

datediff (d,me.startdate,me.enddate) the d specifies for it to return days, the date diff function has several different types of data it can return.

This is getting confusing, i'll try to write out all the code, you will need to substitute your field names:


dim totaldays as string

totaldays = datediff( d, me.startdate, me.enddate)

me.newfield.value = totaldays

you can then use an update query to update the total available hours for each employee by subtracting your new field value from their total available. Hope this helps

 
Old November 27th, 2005, 07:33 PM
Registered User
 
Join Date: Nov 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Bjackman

Thanks for the advice but I need to know the workdays difference between two dates i.e. not including weekends.

Thanks

Ian






Similar Threads
Thread Thread Starter Forum Replies Last Post
DateDiff zahyea .NET Framework 1.x 0 October 16th, 2007 04:38 AM
DateDiff Question ExDb BOOK: Beginning VB.NET Databases 2 August 25th, 2007 10:11 PM
datediff ebburks Access 2 July 28th, 2006 06:58 PM
DateDiff?? ( w/o weekends... ) underscore10304 Access VBA 7 January 24th, 2006 08:55 AM
help with DateDiff isaac2004 Classic ASP Basics 9 January 16th, 2006 02:34 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.