[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

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.
5 Solutions
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?

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now