Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 26th, 2005, 05:44 PM
Registered User
Join Date: Nov 2005
Location: , , United Kingdom.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using DateDiff


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!



Reply With Quote
  #2 (permalink)  
Old November 27th, 2005, 01:03 AM
Authorized User
Join Date: Nov 2003
Location: Hillsboro, Ohio, USA.
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to bjackman Send a message via MSN to bjackman

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

Reply With Quote
  #3 (permalink)  
Old November 27th, 2005, 07:33 PM
Registered User
Join Date: Nov 2005
Location: , , United Kingdom.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts

Hi Bjackman

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



Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 10:15 PM.

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