Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
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 December 6th, 2007, 01:08 PM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default Subtract date

Hi,

I'm having a problem subtracting a date. I have a weekending date and then the user selects if the week is 5,6, or 7 days long. I'm using the dateadd function to subtract the selected days from the weekending date. For some dates it works, but for others it is one day off. Any ideas why this may be happenning? This is what I have set up:
    iRange = Me.cboDays.Value
    Me.txtStop = Me.txtStop1 & "/" & Me.txtStop2 & "/" & Me.txtStop3
    Me.txtStart = DateAdd("d", (-iRange), Me.txtStop)

Thanks,
Dave

 
Old December 6th, 2007, 05:51 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

The problem is in the assignment of Me.txtStop. From the way you're setting it up, you're going to end up with text "12/6/2007" instead of a real date. Therefore, you need to convert it.

Me.txtStop = CDate(Me.txtStop1 & "/" & Me.txtStop2 & "/" & Me.txtStop3)

or better yet

Me.txtStop = DateSerial(CInt(Me.txtStop3), CInt(Me.txtStop1), CInt(Me.txtStop2))

Then the DateAdd will know what to do with Me.txtStop.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old December 7th, 2007, 03:58 PM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Serrano, that makes sense.
I used the dateserial suggestion and it seems to be consistent now. The DateAdd was coming back with a start date one day earlier than it should be, but I made it subtract 1 from the iRange and it looks like it is working fine now.

Thanks,
Dave





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to subtract hours from sysdate in oracle mcmouli Oracle 4 January 7th, 2011 06:52 AM
Automatically Subtract the Value deepcover1 Access 4 March 14th, 2006 08:33 AM
subtract times in XSLT nabrown78 XSLT 1 April 19th, 2005 03:43 AM
Can't subtract days from a date Please Help Arsi C# 1 January 6th, 2005 03:34 PM
DTS Import ( Date string to Date field) gfowajuh SQL Server 2000 1 September 30th, 2003 06:28 AM





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