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 January 23rd, 2008, 07:11 AM
Authorized User
 
Join Date: Nov 2006
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to sanjivbshinde
Default Access DateTime

Hello friends,
        I have on table with fields pid,startdate and enddate.
e.g
there is one record in above table as "111,21/1/2008,21/3/2008"

now when i pass start date and end date then i want records with 3 rows
as 111 21/1/2008
   111 21/2/2008
   111 21/3/2008
means one month interval
so please tell me using MS Access how to do this?

Thanks

 
Old January 23rd, 2008, 12:47 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Here is what I would to calculate these values. I am not sure what you are doing with them (report or new records etc) so not sure what other code you will need:

Dim dtStart As Date
Dim dtEnd As Date
Dim iPID As Integer
Dim dtMid As Date

iPID = Me.PID
dtStart = Me.StartDate
dtEnd = Me.EndDate

'Create recordset to store data
'Post record for Start Date

rs.AddNew
rs("PID") = iPID
rs("Dates") = dtStart
rs.Update

dtMid = dtStart

Do Until dtMid >= dtEnd
   dtMid = DateAdd("m", 1, dtMid)
     rs.AddNew
     rs("PID") = iPID
     rs("Dates") = dtMid
     rs.Update
Loop

rs.AddNew
rs("PID") = iPID
rs("Dates") = dtEnd
rs.Update

rs.Close

This will allow you to have n number of months each time.

I would do this instead, however:

pid,startdate and months
"111,21/1/2008,2"

Did that help?


mmcdonal

Look it up at: http://wrox.books24x7.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
datetime scandalous SQL Server 2005 3 December 19th, 2007 10:33 AM
DateTime pzmrcd C# 3 July 26th, 2007 06:35 AM
Retrival of Server Side DateTime with ACCESS dubey.kuldeep Access 2 November 1st, 2005 12:37 PM
Easy Ques -- Datetime validation for Access bekim Access 2 June 16th, 2005 01:52 PM
UTC DateTime to Local DateTime r_ganesh76 SQL Server 2000 1 April 4th, 2005 08:21 AM





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