adding new records with dates

I have a table/query/form with the following record:-
 I provide service to the customer at regular intervals - weekly, fortnightly, monthly - over the course of a year
I want to be able to generate a series of new RECORDS by adding to the date

I want to be able to fill in the initial details then tell it to add a specific number of days (maybe 7, 14, or 28, or something else altogether) to the date, and to do this a specified number of times.
I thought I might have found an answer, but I seem to have lost it.
I would expect to need two additional fields, Frequency and No of Times, and perhaps an append query, but I just cannot get my head around it.

Most answers are about two date fields (startdate/Enddate) and adding to the first to generate the latter.  Also, they assume that the interval will be constant (always add 7 dayes).This doesn't do what I want.

Can It be done?  I am an amateur when it comes to VBA.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sure you want to do something like this in VBA:

sub AddDates
    dim datStartDate as Date, intFrequency as Integer, intNumTimes as Integer, i as Integer
    dim strSQL as String, datInsert as Date

    datStartDate = cdat(Forms!YourForm!tbStartDate)
    intFrequency = Forms!YourForm!tbFrequency
    intNumTimes = Forms!YourForm!tbNumTimes

    for i = 0 to intNumTimes-1
         datInsert = datStartDate + i * intFrequency
         strSQL = "INSERT INTO YourTable (YourDateField) VALUES (#" & format(datInsert, "mm/dd/yyyy") & "#)"
        currentdb.execute strSQL
    next i
end sub
You'd run this from a form which I called YourForm here which has fields for the StartDate named tbStartDate, the Frequency named tbFrequency and the Number of Times named tbNumTimes
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use an INSERT statement to add new records, and you can use loops for your Frequency and Dates:

Dim iFreq as Integer  
Dim iInterval as Integer
Dim dDate as Date

iFreq = 4 '/ adds 4 records
iInterval = 14 '/ space them 14 days apart
dDate = Date '/ default to starting point of Today

Dim i As Integer

For i = 1 to iFreq
  Currentdb.Execute "INSERT INTO YourTable([Date]) VALUES(#" & dDate & "#)"
  dDate = DateAdd("d", dDate, iInterval)
Next i

This would add 4 new records, spaced 14 days apart, from today's date. If you want to have user inputs for those values, then do this:

1) Add 3 Textboxes to your form: txFreq, txInterval, and txStart
2) Change the code above to this:

Dim iFreq as Integer  
Dim iInterval as Integer
Dim dDate as Date

iFreq = Me.txFreq
iInterval = Me.txInterval
dDate = Me.txStart

Dim i As Integer

For i = 1 to iFreq
  Currentdb.Execute "INSERT INTO YourTable([Date]) VALUES(#" & dDate & "#)"
  dDate = DateAdd("d", dDate, iInterval)
Next i

So if you wanted to add 3 new events, spaced 30 days apart, and starting from November 1st, you'd enter these values in your form's textboxes:

Me.txFreq: 3
Me.txInterval: 30
Me.txStart: 11/01/2011

Either of those code blocks could be run from a Button click. Do you know how to add code to a Button's click event?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You can do it also without VBA.
Look qry1 in included example.
tbl0 - table with your parameters (you can take this data from form)

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
Another method would be to create another table (I call mine tbl_Numbers) which contains a single field (intNumber) and only atenolol records (the values 0-9).  This table becomes the source for a query (qry_Numbers) that looks like the fooling and returns numbers from 0-99.

Select Tens.intNumber * 10 + Ones.intNumber
From tbl_Numbers as Tens, tbl_Numbers as Ones

From this query, you can write another query that will load all of these new dates at once.

Insert into your table (Customer, job date)
Select [x], date add("d", intNumber * interval, date())
From qry_Numbers
Where intNumber >0 and intNumber < intNumberOfTimes
Dale FyeCommented:
Previous post assumes that:
1. You will replace [x] with the customers ID
2. That JobID is an auto number field

It also has an error in the where clause, which should read:
msmerryAuthor Commented:
I used LSMConsulting's second answer, because it was extremely clear, could be lifted straight into my database (with the necessary alterations to names) and did exactly what I wanted - almost.  One problem I had was that the date format was unreliable - it would add the ten entries, but two out of three would be formatted for US, and I live in Australia; however, I solved that problem by using
format(datInsert, "mm/dd/yyyy")
from kmslogic's answer, and fixing that for Australia as dd/mm/yyyy.
The other good point about LMSConsulting was that s/he asked the question at the end "Do you know how to add code to a Button's click event?" which was enough of a prompt for me to ba able to work it out.  Thank you.

Als315 and fyed gave solutions that I could see where they were heading, but couldn't quite work out how to use them.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.