Access 2010 VBA Change dates in loop

Question

I am trying to run an append query to update a table based on the first 30 records for parameters in an sql statement. All of the data resides in an Access 2010 database, and I would like to run the query based off of a button on a form.

I am new to vba and assembled the following code based off of posts.

Option Compare Database

Private Sub Command3_Click()
Dim sql As String
Dim i As Integer
Dim j As Integer
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strTerritory As String

Set dbs = CurrentDb

strTerritory = "Alex Hernandez"
strSQL = "INSERT INTO tblWeather30DayMovingFinal ( NEW, RptdDate, [Clm Nbr], WeatherLimit )  SELECT TOP 30 tblWeather30DayMoving.[NEW], tblWeather30DayMoving.[RptdDate], tblWeather30DayMoving.[Clm Nbr], 1 AS WeatherLimit FROM tblWeather30DayMoving WHERE (((tblWeather30DayMoving.NEW)= strTerritory ) AND ((tblWeather30DayMoving.RptdDate) Between #" & i & "/1/" & j & "# And #" & i & "/28/" & j & "#)); "

Set rst = dbs.OpenRecordset("tblWeather30DayMoving", dbOpenTable)

With rst

 For j = 2003 To 2013
For i = 1 To 12
If Not (rst.EOF And rst.BOF) Then
   .MoveFirst
   Do
     CurrentDb.Execute strSQL
     .MoveNext
   Loop Until .EOF
 End If
Next i
Next j

End With

Set rst = Nothing
End Sub

I receive the following error message. I am trying to figure out how to get the loop to fill my date references in the sql.

Run-time error '3075':

Syntax error in date in query expression '(((tblWeather30DayMoving.NEW)- strTerritory ) AND ((tblWeather30DayMoving.RptdDate) Between #0/1/0# And #0/28/0#)'.

Any idea how to pass i and j to the sql statement instead of the 0's that are currently showing?

Answer 1

You are setting the strSQL string outside of your loop.

At this point, the values of i and j are 0.

You need to assign value to strSQL inside of the second loop:

For j = 2003 To 2013
    For i = 1 To 12
        strSQL = "INSERT INTO tblWeather30DayMovingFinal ( NEW, RptdDate, [Clm Nbr], WeatherLimit )  SELECT TOP 30 tblWeather30DayMoving.[NEW], tblWeather30DayMoving.[RptdDate], tblWeather30DayMoving.[Clm Nbr], 1 AS WeatherLimit FROM tblWeather30DayMoving WHERE (((tblWeather30DayMoving.NEW)= strTerritory ) AND ((tblWeather30DayMoving.RptdDate) Between #" & i & "/1/" & j & "# And #" & i & "/28/" & j & "#)); "

        If Not (rst.EOF And rst.BOF) Then
           .MoveFirst
           Do
             CurrentDb.Execute strSQL
             .MoveNext
           Loop Until .EOF
        End If
    Next i
Next j

Answer 2

I did it in notepad and not tested, but here is the idea:

  Option Compare Database
  option explicit

  Private Sub Command3_Click()
  Dim sql As String, sql2 as string
  Dim i As Integer
  Dim j As Integer
  Dim rst As DAO.Recordset
  Dim dbs As DAO.Database
  Dim strTerritory As String

  Set dbs = CurrentDb

  strTerritory = "Alex Hernandez"
  sql = "INSERT INTO tblWeather30DayMovingFinal ( NEW, RptdDate, [Clm Nbr], WeatherLimit )  " & _
        "SELECT TOP 30 tblWeather30DayMoving.[NEW], tblWeather30DayMoving.[RptdDate], tblWeather30DayMoving.[Clm Nbr], 1 AS WeatherLimit " & _
        "FROM tblWeather30DayMoving WHERE (((tblWeather30DayMoving.NEW)= strTerritory ) AND ((tblWeather30DayMoving.RptdDate) Between #mm/01/yyyy# And #mm/28/yyyy#)); "

  Set rst = dbs.OpenRecordset("tblWeather30DayMoving", dbOpenTable)

  With rst

   For j = 2003 To 2013
  For i = 1 To 12
  If Not (rst.EOF And rst.BOF) Then
     .MoveFirst
     Do
       sql2 = replace(1, sql,"yyyy", cstr(j))  'replace "jjjj" by year 
       sql2 = replace(1,sql2,"mm", format(i,"00"))  'replace "mm" by month 
       debug.print sql2
       CurrentDb.Execute sql2   'this can be REM'd once it is all working
       .MoveNext
     Loop Until .EOF
   End If
  Next i
  Next j

  End With

  Set rst = Nothing
  End Sub

Also note that you did not set Option Explicit, and you are mixing variable names between strSql and Sql.

I created the sql string using silly dates, and then replaced them by the appropriate figues in the loop, just before execution. Not the most efficient, but I find it easy and readable.