Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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, 02:31 PM
Registered User
 
Join Date: Nov 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Workdays Using DateDiff

Hi

I am very new to MSAccess 2003. I hope someone can help! I have an employee database with three tables. One contains employee info, one contains sickness info (date from and date to) and the other contains holiday info (date from and dateto). I am trying to get the form to display and calculate the difference between the two dates. For example:
An employee has 20 days holiday entitlement, and I enter a new holiday for him in the holiday table on the form (table is related to employees). I want the form to work out/display how many working week days the holiday is for and to deduct the amount of holidays taken from the entitled 20.

I've tried DateDiff("w",[HolStartDate]-[HolEndDate]) but I don't know what I am doing wrong!

Please help and be gentle - I'm a newbie!!

Thanks

Ian

 
Old November 26th, 2005, 03:28 PM
Registered User
 
Join Date: Nov 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Apologies, I'm using this:

=DateDiff("w",[HolFromDate],[HolToDate])

But it's not working properly?!

 
Old November 26th, 2005, 03:38 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Hi Ian,

I am racing to finish another couple of projects, but if it helps...

DateDiff with "W" as the first option will give you the number of weeks difference. WIth "D" it will give you the number of days.

Excel has a function called NetWorkDays that allows you to pass in an array of holidays, and excludes those as well as weekends. I am not sure if that is of any help. I'll have a better look at it once I get done with my work, but hopefully that will give you somewhere to start.

All the best,

Mike

Mike
EchoVue.com
 
Old November 26th, 2005, 03:50 PM
Registered User
 
Join Date: Nov 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Mike

Thanks for the help. I need the weekdays only - not including weekends. Any advice?

Thanks

Ian

 
Old November 26th, 2005, 03:55 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Try this url, it may give you a start...

http://www22.brinkster.com/accessory/modules/003.shtml

Mike

Mike
EchoVue.com
 
Old November 26th, 2005, 04:17 PM
Registered User
 
Join Date: Nov 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks again Mike. Sorry to be a pain but how do I use this function in a form?

Ian

 
Old November 26th, 2005, 05:28 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Hi Ian,

What you would need to do, is to imlement this in the VBA code behind the form - I am guessing that you are probably not familiar with VBA - it is a lot like Visual Basic.

I am up against some tough deadlines right now, but I may have some extra time tonight or tomorrow. One thing that may be useful is posting in in the Access forum - There are some pretty helpful guys in that one as well.

HTH

Mike

Mike
EchoVue.com
 
Old November 26th, 2005, 09:46 PM
Registered User
 
Join Date: Nov 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Mike

Still no response from the other Access forums. I've got a headache now!javascript:insertsmilie(':(')







Similar Threads
Thread Thread Starter Forum Replies Last Post
datediff ebburks Access 2 July 28th, 2006 06:58 PM
help with DateDiff isaac2004 Classic ASP Basics 9 January 16th, 2006 02:34 AM
Using DateDiff iancrabtree Access 2 November 27th, 2005 07:33 PM
Access Workdays calculation doug.hall Access 1 April 26th, 2005 02:19 PM
Calculate Workdays minus holidays woodan Access 1 February 11th, 2004 04:17 PM





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