Crrentdb.Execute not working inside a while loop. (1 Viewer)

[abzalali]

Dim t_id, ag_id, rate, incr_rate As Integer
    Dim stDate, nxstDate, nxenDate, nxyrDate, enDate As Date
    Dim StrSQL As String
    
    
    t_id = Me.tenant_id
    ag_id = Me.id
    stDate = Me.startdate
    enDate = Me.enddate
    rate = Me.initial_rate
    incr_rate = Me.increase_rate
    
    nxyrDate = stDate
    Do While nxyrDate < enDate
        nxstDate = nxyrDate
        nxyrDate = DateAdd("yyyy", 1, nxstDate)
        nxenDate = DateAdd("d", -1, nxyrDate)
        
        'Debug.Print t_id, ag_id, nxstDate, nxenDate, rate
        [B]StrSQL = "INSERT INTO tbl_agreement_years1(tenant_id, ag_id, interval_start, interval_end, rate)" & _
            "VALUES (t_id, ag_id, nxstDate, nxenDate, rate)"
        CurrentDb.Execute StrSQL, dbFailOnError[/B]
        rate = rate + incr_rate
        
    Loop

This set of code return like below:

 4             1            09/09/2016    08/09/2017     95 
 4             1            09/09/2017    08/09/2018     105 
 4             1            09/09/2018    08/09/2019     115

StrSQL = "INSERT INTO tbl_agreement_years1(tenant_id, ag_id, interval_start, interval_end, rate)" & _
            "VALUES (t_id, ag_id, nxstDate, nxenDate, rate)"
        CurrentDb.Execute StrSQL, dbFailOnError

With this line I tried to insert this set of record into a table then it's showing error: run-time error 3061, Too few parameters, Expected 5.

Please help to get out of here :banghead:

 

[arnelgp]

Use this syntax:

INSERT INTO ....
Values (" & t_id & "," & ag_id & ",#" & Format(nxstDate,"mm/dd/yyyy") & "#,#" & Format(nxenDate,"mm/dd/yyyy") & "#," & incr_rate & ")"

 

[abzalali]

@arnelgp, Thank you so much, It works nicely.

 

[NauticalGent]

Ok Arnelgp, care to tell us why your code worked and the OP's didn't?

 

[Frothingslosh]

The OP was accidentally passing string literals, not the values included in the variables. AGP's code splices in the actual values.