Wrox Programmer Forums
| 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 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
  #1 (permalink)  
Old July 27th, 2006, 03:03 PM
Authorized User
 
Join Date: May 2006
Location: Elk Grove, CA, USA.
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default datediff

Hi
I am using the datediff to get the difference between two days from two form control fields.
I have setup one table that I have enter dates from 1/01/2006 to 12/31/2006. I have made a query that omits holidays and weekends and assigned them to each one of the form control fields. However when I use the datediff function to show the day difference from the two diff dates selected in each field it is still including weekends and holidays in the date difference. How do I get it to just give me the difference between the days in the query?

EB
__________________
EB
  #2 (permalink)  
Old July 28th, 2006, 09:42 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

This is a problem I have found with VBA. In the MSDN refernce for the DateDiff Function, MS refernces "w" as the interval to use for "Weekday." However, w returns the weeks.

What I have done is to take each date, check its day of the week value, if it is between 2 and 6, add 1 to a variable, and then display the total. This wouldn't help much in a query. Perhaps you can create a function that does this when the fields in your table are updated, and actually store the values in the table.

I have written the function if you want to use it.

HTH


mmcdonal
  #3 (permalink)  
Old July 28th, 2006, 06:58 PM
Authorized User
 
Join Date: May 2006
Location: Elk Grove, CA, USA.
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you very much. However, I went a different route. I am able to get what I needed.
Again, thank you

EB


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?? ( 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
Using DateDiff iancrabtree Access 2 November 27th, 2005 07:33 PM





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