adding new records with dates

Posted on 2011-10-03
Last Modified: 2012-06-27
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.
Question by:msmerry
    LVL 16

    Assisted Solution

    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
    LVL 84

    Accepted Solution

    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?

    LVL 39

    Assisted Solution

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

    LVL 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    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
    LVL 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    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:

    Author Closing Comment

    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This collection of functions covers all the normal rounding methods of just about any numeric value.
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now