insert distinct dates into table from code 1

[kim21]

I've got the following code below, which kind of does what I want but is missing something fundamental.
the runsql command inserts the right number of rows but not the right dates (its always the same date as determined by idate)
if the review_frq is quarterly and the no_years is 1 and the initial_month is 01/01/02 then I'd want (this is the bit that i can't get to work) 2 records into the table with dates of 01/01/02, 01/04/02, 01/07/02 and 01/10/02

can someone help please?????
kim

Dim myNum As Integer
Dim timesby As Integer
Dim loopby As Integer
Dim idate As Date
Dim idateadd As Integer
Dim x As Integer

If Me.review_frq = "Monthly" Then
myNum = 12
idateadd = 1
ElseIf Me.review_frq = "Quarterly" Then
myNum = 4
idateadd = 3
ElseIf Me.review_frq = "Half-Yearly" Then
myNum = 2
idateadd = 6
ElseIf Me.review_frq = "Annually" Then
myNum = 1
idateadd = 12
End If

timesby = Me.no_years
loopby = myNum * timesby

idate = DateAdd("m", idateadd, Format(Me.initial_month, "long date&quot)

For x = 1 To loopby
DoCmd.RunSQL "INSERT INTO tblreview(strat_met, review_month) values(" & Me.strat_met & " , #" & Format(idate, "long date&quot & "#)"
Next x

 

[zerosandones]

Hi Kim,

Your problem lies in the fact that your iDate variable is outside your FOR/NEXT loop ... it needs to be incorporated into the looping code.

I hope you don't mind if I re-write your code to better illustrate how to achieve what you are looking for:

Dim interval As Integer
Dim timesby As Integer
Dim loopby As Integer
Dim monthsadd As Integer
Dim idate As Date
Dim x As Integer

SELECT CASE Me.review_frq
CASE "Monthly"
interval = 12
CASE "Quarterly"
interval = 4
CASE "Half-Yearly"
interval = 2
CASE "Annually"
interval = 1
END SELECT

timesby = Me.no_years

' Calculate loopby number less one to skip starting date
loopby = (interval * timesby) - 1

' First insert the starting date
DoCmd.RunSQL "INSERT INTO tblreview(strat_met, review_month) values(" & Me.strat_met & " , #" & Me.initial_month & "#)"

' Next, loop through and insert the remaining intervals
For x = 1 To loopby
monthsadd = x * 12 / interval
idate = DateAdd("m", monthsadd, Me.initial_month)
DoCmd.RunSQL "INSERT INTO tblreview(strat_met, review_month) values(" & Me.strat_met & " , #" & idate & "#)"

Next x

Hope this helps Kim ... if you don't want to deal with my code, just incorporate your idate calculation within your FOR/NEXT loop.

00001111s

 

[gwilym40]

Brilliant! thanks