Repeat AddDate calc & store

[Roscoe24]

I need to calc (using AddDate - which works fine) a new date for each month after [DtEntered] for up to 5yrs ahead. (Can use BETWEEN statement OK).

Each date then to be entered as a NEW record in a table with a unique ID. (i.e. 60 unique records)

I don't mind if I use form, qry or VBA to do this.

I've just spent 4 hours searching forum and not found answer.

Can anyone please help? I've got a complete brain block on it!

Thanks in anticipation.

 

[Roscoe24]

Whilst I fully appreciate the rules of normalisation re not storing calculated data (as per Similar Threads below) ... in this case they must be precalculated and stored for Accountancy / Tax purposes.

So ... has anyone any suggestions taking it as red that they must be stored? I notice that Pat Hartman (guru) even agrees there are times when this must be done!

 

[Galaxiom]

There is nothing wrong with storing the dates if there is information to be stored against them.

However if you are simply storing the dates alone then there is really no valid reason to store that list. It can be regenerated at any time from a formula and as such it is effectively precalculated and stored as a formula rather than individual records.

However if you insist, the records can be written to a table via a recordset. A loop adds new records to the recordset by incrementing the date according to your formula.

 

[Roscoe24]

Thanks Galaxiom. Yes, there is much more data being stored against each subsequent date but I completely understand and agree with what you are saying.

Can you give me a clue as to where to find right code for the recordset function to achieve something similar to what I'm looking for? I am most grateful to you for your help. I will put a [DtStart] and [DtEnd] in the code to limit the Loop function.

 

[Galaxiom]

Open a recordset against the table.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT datefieldname FROM yourtable")

Then inside the loop which you seem to know how to set up:

With rs
   .AddNew
   !datefieldname = dteNext
   .Update
End With

dteNext = DateAdd("d", intInterval, dteNext)

 

[Roscoe24]

You're a star! Thanks so much. Perfect.

 

[Roscoe24]

Uuuuummmmm ... sorry .... need a bit more ......

I want to specify how many months to calculate eg 6 months or 12 months or 5 years etc. Can someone give me the whole code for this recordset question - with regard to the EOF element particularly.

What I'm up to, in case it helps, is predicting invoice dates and values in the future, for the specified period. These need calculating ahead of time and storing because of future Tax and other commitments to be outlayed by calculated results.

Having spent, now, a whole day on this one element of the system I'm close to tearing my hair out.