Copy record 'n' number of times, incrementing date... (1 Viewer)

MackMan · Jun 3, 2015

Hi everyone.

Is it possible to copy a record 'n' number of times, incrementing the date by either days, months, weeks etc?

I have a regular payments table that will need to be edited at some point should either dates, or amounts change, but... regardless of the size of the table, it's not a problem, as these will be moved to a different table and deleted once paid.

Given the example, would it be possible to copy this record and additional 5 times, incrementing the date by one week,

As always appreciate your help.

CJ_London · Jun 3, 2015

some vba code along the following lines

for I=0 to x-1
...insert record with a date calculated as dateadd("ww",i,startdate)
next i

BlueIshDan · Jun 3, 2015

BlueIshDan · Jun 3, 2015

CJ_London said:
some vba code along the following lines

for I=0 to x-1
...insert record with a date calculated as dateadd("ww",i,startdate)
next i

You could also store the end date automatically as well. Saves from human error.

MackMan · Jun 3, 2015

Thanks guys. Appreciated.

This will give me a bit to work on. Will let you know the outcome.

MackMan · Jun 6, 2015

Apologies for the lateness in replying.

I had to make a small amendment to the structure of this.
The main table [tblBillInfo] holds the details of the Regular Payments, and I've created another table [tblBillSeries] to hold each record according to the number of payments etc, and will use the latter table to move the records across when they are paid.

I've created the following to copy the details to the other table...

Code:

Dim strSQL As String
Dim db As Database
Dim i As Integer
Dim NewID As Long
 
Set z = Me.HOwMany
Set db = CurrentDb()

 If Me.Dirty Then
    Me.Dirty = False
End If
 
NewID = Me.BillInfoID

 For i = 1 To z
      strSQL = "INSERT INTO [tblBILLSeries] ( BILLINfoID, Payee, HowMany, DueDate, Category, SubCategory, Credit, Debit, Amount) " & _
      "SELECT " & NewID & " As NewID, Payee, HowMany, DueDate, Category, SubCategory, Credit, Debit, Amount " & _
      "FROM [tblBillInfo] WHERE BillInfoID =  " & Me.BillInfoID & ";"
      DBEngine(0)(0).Execute strSQL, dbFailOnError
    
    
Next i

 db.Close

 Me.frmBillSeries.Requery

My question is...

Am I still able to add the Dateadd(*,*,*) function to SQL?

Or is there another way to copy from one table to another, where I can?

As always, really appreciate your help!

CJ_London · Jun 7, 2015

Am I still able to add the Dateadd(*,*,*) function to SQL?

yes, it is a standard function but you would build it into your sql string as you have with NewID

MackMan · Jun 7, 2015

Hi CJ.

Thanks for you reply (And Patience)!!

Ran into a problem where I run it, and I get all the number of occurrences, but...
All dates are "31/12/1899". I also noticed in the Immediate Window that (in this instance) Interval "M", is Empty?)

Here's the code...

Code:

Dim strSQL As String
Dim db As Database
Dim i As Integer
Dim NewID As Long
Dim StartDate As Date
Dim z As Double
 
Set db = CurrentDb()
 
If Me.Dirty Then
    Me.Dirty = False
End If
  
 NewID = Me.BillInfoID
StartDate = Me.DueDate
Interval = Me.cbointerval
z = Me.HOwMany
  
 For i = 1 To z
    
    strSQL = "INSERT INTO [tblBILLSeries] ( BILLINfoID, Payee, HowMany, DueDate, Category, SubCategory, Credit, Debit, Amount) " & _
    "SELECT " & NewID & " As NewID, Payee, HowMany," [COLOR=darkred][B]& DateAdd(Interval, z - 1, StartDate) &[/B][/COLOR] " AS DueDate, Category, SubCategory, Credit, Debit, Amount " & _
    "FROM [tblBillInfo] WHERE BillInfoID =  " & Me.BillInfoID & ";"
    DBEngine(0)(0).Execute strSQL, dbFailOnError
    
    
    Next i
 db.Close

I've set the destination tables format of the date field to "Short Date" and the table is also "Date/Time - Short Date"

I Would appreciate some help.

As always, I'm grateful, and the first to admit I've done it wrong.

CJ_London · Jun 7, 2015

1. you need a lowercase 'm'

2. check your cboInterval control

CJ_London · Jun 7, 2015

also I would have though that 'z-1' should be 'i'

MackMan · Jun 7, 2015

Getting there! I sorted out the 31/12/1899 by adding the following to the SQL Statement... and formatted the date correct to UK using ...

Code:

 ...[COLOR=black] HowMany," [COLOR=darkred][B]& Format(DateAdd(Interval, i - 1, StartDate), "\#mm\/dd\/yyyy\#") &[/B][/COLOR] "...[/COLOR]

Yes, and I admit defeat... it was "I" as I wanted to add One... not say.. 5!

MackMan · Jun 7, 2015

...And by I .. I mean.. "i"

Thanks again CJ!

Legend!

Copy record 'n' number of times, incrementing date... (1 Viewer)

MackMan

Registered User.

Attachments

CJ_London

Super Moderator

BlueIshDan

☠

Attachments

BlueIshDan

☠

MackMan

Registered User.

MackMan

Registered User.

CJ_London

Super Moderator

MackMan

Registered User.

CJ_London

Super Moderator

CJ_London

Super Moderator

MackMan

Registered User.

MackMan

Registered User.

Similar threads

Users who are viewing this thread

Copy record 'n' number of times, incrementing date... (1 Viewer)

Registered User.

Attachments

Super Moderator

&#9760;

Attachments

&#9760;

Registered User.

Registered User.

Super Moderator

Registered User.

Super Moderator

Super Moderator

Registered User.

Registered User.

Similar threads

Users who are viewing this thread

☠

☠