By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

Create Records in Date Range (VBA)

P: 1
Here is my problem. I have a lot of different fields that all revolve around two date fields, BeginDate and EndDate. The user enters information into both these fields along with other data. My problem is that I need the begin and endDates that they enter to be broken up by months. For example, if a user enters a begin date of 1/20/2009 and end date of 5/20/2009 access would automatically create 5 new records looking something like this (and duplicating the data in the other fields along with it):

BeginDate EndDate
1/20/2009 1/31/2009
2/1/2009 2/28/2009
3/1/2009 3/31/2009
4/1/2009 4/30/2009
5/1/2009 5/20/2009

Below is some logic that might be used to do this, I'm thinking a loop could be the answer but don't know exactly how to set it up in VBA... any help is appreciated!
Expand|Select|Wrap|Line Numbers
  1. Dim LngMTH as Long
  2. '1st record BeginDate as value
  3. LED=Dateserial(Year(BeginDate),Month(BeginDate)+1, 0) ' This gives the last day of the month.
  4. exit or goto
  5. LngMTH=Month(BeginDate)
  6. Do until LngMTH=Month(EndDate)+1
  7. BeginDate=Dateserial(Year(BeginDate),LngMTH,1)
  8. EndDate=Dateserial(Year(BeginDate),LngMTH+1,0)
  9. LngMTH=LngMTH+1
  10. Loop
  11. 'lastrecord
  12. BeginDate=Dateserial(Year(BeginDate),LngMTH,1)
  13. EndDate as given value
Jun 21 '09 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,709
It seems you need to add in the part of the code that adds new records. Currently your pseudo-code loop does nothing other than process the loop.

Here is a link to basic DAO recordset processing (Basic DAO recordset loop using two recordsets). .AddNew() & .Update() are the methods you need to prepare and save the data.

Please have a go with this if you can. If you have further problems, please post the code you've attempted with any error messages and which lines they pertain to.

Welcome to Bytes!
Jun 22 '09 #2

ADezii
Expert 5K+
P: 8,679
Just subscribing, as NePa has stated, let us know if you continue to have problems.
Jun 22 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.