Copy record 'n' number of times, incrementing date...

[MackMan]

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]

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]

 

[BlueIshDan]

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]

Thanks guys. Appreciated.

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

 

[MackMan]

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...

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]

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]

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...

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]

1. you need a lowercase 'm'

2. check your cboInterval control

 

[CJ_London]

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

 

[MackMan]

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 ...

 ...[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]

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

Thanks again CJ!

Legend!